Database connection fine on localhost, doesn't work on web.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Database connection fine on localhost, doesn't work on web.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-23 : 08:18:21

Nate writes "I have a web app that connects to two seperate DB's. Both DB's reside on the same server. Both connections are nearly identical in syntax except for the UserID and Password, as follows:
(names of database objects have been changed to protect their identity)
Conn1 -
Application("conn_ConnectionString") = "Provider=SQLOLEDB;Persist Security Info=False;User ID=User1;Initial Catalog=DataOne;Data Source=Server;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BigServer;Use Encryption for Data=False;Tag with column collation when possible=False;PASSWORD=password1;"
Application("conn_ConnectionTimeout") = 15
Application("conn_CommandTimeout") = 30
Application("conn_CursorLocation") = 3
Application("conn_RuntimeUserName") = "User1"
Application("conn_RuntimePassword") = "password1"
Conn2 -
Application("connB_ConnectionString") = "Provider=SQLOLEDB;Trusted_Connection=Yes;User Id=User2;Initial Catalog=DataTwo;Data Source=Server;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BigServer;Use Encryption for Data=False;Tag with column collation when possible=False;PASSWORD=password2"
Application("connB_ConnectionTimeout") = 15
Application("connB_CommandTimeout") = 30
Application("connB_CursorLocation") = 3
Application("connB_RuntimeUserName") = "User2"
Application("connB_RuntimePassword") = "password2"

The User Profiles that correspond to the UserID's and Password's have been created in their respective DB's and their profiles have Execute permissions.
When I run it on my localhost copy everything works fine, I can pull in the data off of DB2. However when I updated to the web and run it, I get this:
"Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."
If both connections are almost the same, why would one connection work (to DB1), and this other connection throw an error (to DB2)? I hope I have provided enough information for you to provide some help.
Much appreciated,
Nate"

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-02-23 : 12:55:27

Trusted_Connection=Yes is NT
from the web it would be the web service account which would need to have security established in Sql Server.

You can do anything at www.zombo.com

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-03-04 : 18:42:31

I just worked through the same thing with a Microsoft tech. This is a Kerberos Issue. You can do the following. Basically Kerberos is faily, and the IIS or Web Server is trying to authenticate using NTLM, but if you have a client connecting to a web server wich in turn connects to a SQL Server, you have what it known as a double hop situation. However the reason that the authentication failed is because Kerberos failed. Kerberos usually fails because of packet frgmentation, or your packets are too large. Here are the list of things, that fixed th problem for us.

1.)Set the Max Packet Size in the registry of the Web Server, and the SQL Server. This is shown in the following MS Knowledge Base Article 244474.

2.)Use setspn.exe from the Windows2000 disk under support tools. You must install the support tools before using. Check to make sure that both the SQL Server, and IIS Server have SPN's for http and MSSQL. if not refer to the following MS KB Article. 811889. The message in this article is not the one you are getting, but is the same fix, and is the one MS Tech used.

3.) Make sure in active directory that both computers are trusted for delegation. (Active Directory Users and Computers)

4.) In the root of the inetpub directory there are admin scripts. you need to run the following on the adsuti. "cscript adsutil.vbs set w3svc/NTAuthenticationProviders “NTLM”" There is also an article on this, but I don't have the number. I think it is contained in the following DB article though. 326985

Please post back if this doesn't work.

"Knowledge is is good to have, but better to share."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources