NT authentication with SQL Server

[Mike Moore]

I have several Views on a Microsoft SQL Server which use the user_name() function to determine the current user, and display only relevant data.

With Basic Authentication or NT Challenge/Response security applied to the ASP pages in Internet Information Server, it is possible to authenticate the Windows NT user through to SQL Server, and thus use the user_name() function to determine the current user. I can't seem to replicate this using ASP-db, though; either a UID and PWD must be supplied as part of the connection string, or, if these are left blank, ASP-db authenticates (if possible) as the user configured in the "Identity" tab of the ASP-db component in MS Transaction Server.

I'm not very familiar with MTS, and haven't been able to find a way to authenticate the NT user from IIS through MTS to SQL Server; does anyone know if this is possible (and, if so, how it can be implemented)?

Thanks in advance.

[Frank Kwong]

Forget ASP-db for the moment. Can you show the ASP code you use to open the datasource w/o using UID and PWD.

FK

------------
Mike Moore at 5/1/01 8:56:01 AM

I have several Views on a Microsoft SQL Server which use the user_name() function to determine the current user, and display only relevant data.

With Basic Authentication or NT Challenge/Response security applied to the ASP pages in Internet Information Server, it is possible to authenticate the Windows NT user through to SQL Server, and thus use the user_name() function to determine the current user. I can't seem to replicate this using ASP-db, though; either a UID and PWD must be supplied as part of the connection string, or, if these are left blank, ASP-db authenticates (if possible) as the user configured in the "Identity" tab of the ASP-db component in MS Transaction Server.

I'm not very familiar with MTS, and haven't been able to find a way to authenticate the NT user from IIS through MTS to SQL Server; does anyone know if this is possible (and, if so, how it can be implemented)?

Thanks in advance.

[Mike Moore]

Indeed; as per MS Knowledge Base article Q247931 (http://support.microsoft.com/support/kb/articles/Q247/9/31.asp), the main connection string I use is of the form "Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=catalogname;Data Source=servername". An alternative is to set up an ODBC data source on the server using the "With Windows NT Authentication" option to verify the authenticity of the login ID, in which case the connection string is simply the Data Source name. In either instance, a typical piece of ASP code to create a recordset would be of the form:

Set Conn = Server.CreateObject("ADODB.Connection&#34
Conn.Open (connection string)
Set RS = Conn.Execute("SELECT * FROM Table/View&#34



------------
Frank Kwong at 5/2/01 12:23:43 AM

Forget ASP-db for the moment. Can you show the ASP code you use to open the datasource w/o using UID and PWD.

FK