Classic ASP Integrated Security in connection string

Question

I have old classic ASP application (not asp.net) that I need to configure to use Microsoft SQL Server 2008. It works well with following config string:

SERVER=myServer;DRIVER=SQL SERVER;DATABASE=myDatabase;UID=sa;PWD=somepass

But I need to use integrated security. But got error in any types of declaration that I tried. Something like that and a lot of variations don't work:

SERVER=myServer;DRIVER=SQL SERVER;DATABASE=myDatabase;Integrated Security=SSPI;

So my question is: how connection string should look like for Classic ASP integrated security. Or maybe additional web server configuration is needed?

Answer 1

Note that this will hit SQL based on the authentication of your asp application pool assuming your using anonymous authentication.

I see you have added a comment noting IIS 5, setting a site to use windows auth on ISS5 is possible by going to the properties of your site, selecting the "Directory Security" tab, then clicking the "Edit" button on the "Anonymous access and authentication control" section. Disable anonymous authentication and tick the "Integrated Windows Security" option.

(NOTE: the webserver will need to be able to authenticate the credentials, so you may run into NTLM and kerberos issues depending on your domain configuration - tread carefully!)

This should execute the asp files as the authenticated user, in which case your connection strings would then be able to use trusted connections.

You have a few choices here depending on which providers you have available to you.

For instance, with the SQL Native Client 9.0 OLE DB provider you could use:

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Native Client 10 is slightly different:

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

I prefer the SQLOLEDB provider (ive had trouble with SQL SERVER driver and VARCHAR(MAX) in the past):

Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=myDataBase;Integrated Security=SSPI;

NOTE: suddenly changing the authentication may break other stuff - i'd certainly just recommend adding a SQL credential on the SQL server and use that in your connection strings instead.