Classic ASP problem connecting to remote SQL Server database

Question

I have a classic ASP app that I am trying to connect to a SQL Server 2008 database on a different server. The ASP app is being served from IIS7 on Windows Server 2008.

I have changed the web site's application pool to run under a specific windows account, that I have verified has access to the database on the remote server.

However, when I run the app in the browser, I get this error:

Application Error 
Number: -2147217843 (0x80040E4D)
Source: Microsoft OLE DB Provider for SQL Server
Description: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. 

Why is it trying to connect using NT AUTHORITY\ANONYMOUS LOGON? Does the App pool identity not apply to classic ASP code? How can I make this connect as a specific user?

EDIT

Here is the connection string I am using:

Provider=SQLOLEDB.1;Data Source=myDbServer;Initial Catalog=myDatabase;Integrated Security=SSPI

Hormone

Answer 1

For a site to use the application pool identity for classic ASP, you need to change the credentials used for Anonymous Authentication. By default, the site will be set to use a specific user, namely IUSR.

Select Authentication from the IIS area of your site, then select Anonymous Authentication followed by Edit. Change from Specific user to Application pool identity.

It's advisable to use Windows authentication (integrated security) over SQL authentication so that you don't have credentials in your config files so that if those files are compromised, you don't lose control of the credentials.

Hairspring

Goddamn, this has been vexing me for days. Thanks for posting a solution. – Commode

Answer 2

Does your app impersonate the caller? You need to enable constrained delegation: Configuring Servers for Delegation.

Pori

Neither servers are members of an Active Directory domain. – Hormone

Integrated Security=SSPI needs NTLM/Kerberos to work. You can either create a domain and use tru Windows Auth, use SQL authentication instead, or create identical local user/password accounts on the two machines and have the app pool use this credential. – Pori

@Remus - "... or create identical local user/password accounts on the two machines and have the app pool use this credential." That's exactly what I have done, but it is not working. I have done this for an ASP.NET site that is working with the same account. This is frustrating. – Hormone

I don't think your classic ASP app pool is actually running as the user you expect it to run. But I'm not familiar enough with classic ASP on IIS7 quirks to guess why. – Pori

Answer 3

In addition to Robin's answer, there is another way to solve this, which is necessary if you are not using Anonymous Authentication. If you are using Windows Authentication (as is my case) you may instead want to define the user in Sites > [site name] > Advanced Settings > Physical Path Credentials.

Although it doesn't look like it from the name of the field, this does actually set the user that is used to authenticate to SQL when Windows Authentication is on and Anonymous Authentication is off.

Unlimited

Answer 4

you should specify a username and password for the connection string www.connectionstrings.com or set the IIS application to run as a specific user however that would then render a lot of the security settings in IIS obsolete.

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;

And have a look here: aspfaq

Lastly, make sure anonymous access is disabled on the IIS site so that it actually impersonates the user you selected instead of passing the anonymous tokens through.

Apetalous

I don't want to disable anonymous access. I am using a form to authenticate users. – Hormone

You shouldn't specify a username and password (i.e. SQL authentication) if you can avoid it. Using Windows authentication (integrated security) means you don't need to put credentials in your config files so that if those files are compromised, you don't lose control of the credentials. I've added an answer which shows how to get Windows authentication working for classic ASP. – Hairspring

@RonnieOverby This shouldn't be the accepted answer! See @RobinM Answer. – Winifield

I can delete this if its not required any more. – Apetalous