authentication to sql 2005 using domain account from ASP

Question

I cannot seem to use a Windows account to access my database from my ASP page. Here is my connection string: PROVIDER=SQLOLEDB;DATA SOURCE=NHA-SQL-I0;UID=DOMAIN\NHA-svcRequestForm;PWD=password;DATABASE=RequestForms

I get the classic: Microsoft OLE DB Provider for SQL Server (0x80040E4D) Login failed for user 'NIRHB\NHA-svcRequestForm'.

This occurs for any domain account I use. Using SQL accounts works fine. Any idea what setting/configuration I am missing?

Thanks

Answer 1

That type of connection string is used for SQL accounts; you're actually trying to connect as a SQL user named "DOMAIN\NHA-svcRequestForm".

Try somethign like this:

PROVIDER=SQLOLEDB;DATA SOURCE=NHA-SQL-I0;DATABASE=RequestForms;Trusted_Connection=YES

And whatever application is trying to connect to the database must be running as the domain user. This means the identity of the application pool must be set to your domain user (if this is a web app in IIS), or the service identity must be the domain user (if a windows service).

Answer 2

Specifying user name and password in connection string works only for SQL authentication.

For integrated authentication (ie. use a 'domain account') you must specify Integrated Security=SSPI instead. You cannot specify an user name explicitly, SQL will authenticate the account running the process macking the connection (ie. ASP account).

If you want to authenticate domain users connected to your site you must turn on impersonation in your app pool and enable constrained delegation. See How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0.

Answer 3

check out http://www.connectionstrings.com/sql-server-2005 they have all the connection strings and how to use trusted connection