October 3, 2001 at 5:36 am
Is it possible to create a trusted connection from an ASP page to SQL Server?
I have tried the following:
<%
' Initialize variables.
Set cn = Server.CreateObject("ADODB.Connection")
' Specify the OLE DB provider.
cn.Provider = "sqloledb"
strUSer = Request.ServerVariables("logon_user")
' Specify connection string on Open method.
ProvStr = "Server=ronnietWork;Database=pubs; Trusted_Connection=yes"
cn.Open provStr
SQL = "select * from authors"
set objRec = cn.Execute(SQL)
%>
<%=objRec("au_lName")%>
This is the error message I get, suggesting that 'null' is being passed through for my trusted connection.
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
If anyone has any ideas as to why this is the case that would be great.
Thanks,
Karl Grambow
October 3, 2001 at 6:41 am
Taken from MSDN:
Q176377 INFO: Accessing SQL Server with Integrated Security from ASP
Microsoft SQL Server Integrated Security requires NTLM authentication in order to map user accounts to SQL Server accounts. This process requires that a token be created during the authentication process. This token requires a the user password to create a private encryption key. Because of this, the token can only be created on a domain controller or the logged on user's machine. Also note that Windows NT 4.0 does not allow the forwarding of such tokens.
With these points in mind you can see that after a Web browser is authenticated by Internet Information Server (IIS), an authenticated connection to the SQL Server is not possible. At this point when IIS attempts to connect to SQL Server via NTLM, IIS does not have the necessary information to complete the NT authentication process.
There are a couple possible workarounds to this limitation:
Host IIS and SQL Server on the Same Machine
By eliminating the need for IIS to create an authenticated connection to SQL Server, you can work around this problem. To do this you must use a data source name (DSN) that does not look out to the network for the SQL Server and instead looks directly to the local machine. This can be done by using the "(local)" setting in a System DSN.
Use Basic Authentication Instead of NTLM in IIS
By using Basic authentication, the password is BASE64 encoded and sent to IIS during the authentication process. With the password, IIS can now complete the NTLM authentication process when connection to SQL Server.
NOTE: This method is not secure. BASE64 encoded passwords can be decrypted by anyone able to sniff network packets over the Internet or intranet.
Map the Anonymous User Account from IIS to a SQL Server Guest Account
This method assumes that all users will have the same level of privileges to the SQL Server resources. This method is most often the LEAST acceptable option.
REFERENCES
For additional information, please see the following article(s) in the Microsoft Knowledge Base:
Q176379 HOWTO: IIS and SQL Server on Separate Machines with Trusted Connection
Q176378 HOWTO: SQL Server with Integrated Security, IIS on Same Machine
Q176380 HOWTO: Using ASP with a SQL Trusted Conn with Guest Acct
October 3, 2001 at 7:26 am
Leon,
thanks for your very detailed reply. Much appreciated. I was worried that you were going to say as much.
I'll look into those options you suggested.
Again, thanks a lot.
Karl Grambow
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply