by Here's one that's consumed too much of my life already :)
When creating an Active Server Page to connect to an ODBC Datasource, it is
required to hard-code the SQL Server Login/Password in the VBScript line:
<% objDBConnection.Open "PhoneList", "Username", "Password" %>
Where PhoneList is an ODBC DSN on the IIS Server. This utilizes SQL
Server's Standard Security model over TCP/IP. SQL Server also supports NT
Integrated Security through Trusted connections over either Named Pipes or
Multi-Protocol network libraries. I would like to be able to utilize SQL's
Integrated Security to authenticate a user to a database from the client's
browser.
IIS Security allows for an NT Challenge/Response authentication, and you
can even get the name of the currently logged-in NT User from the client's
workstation running the browser. (Request.ServerVariables("LOGON_USER"))
Under standard security for SQL, you can pass this variable as a parameter
to the first line above, and if that SQL Login Name has no password, you're
in.
This requires all login passwords in SQL to be empty, or to manage a
password list yourself in another table somewhere.
Since SQL Server can only establish trusted connections through
Multi-protocol clients, and the browser session is pure TCP/IP, you receive
a general network error when trying to establish a trusted SQL connection
from a browser.
I would like to know if anyone has been able to manage Web-based
authentication to SQL Server through the mechanisms already in place in NT
Server/IIS Server, or if this is an issue to be resolved in future releases
of the products?