About > Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection

Question

I have an aspx page in C# under .net 4.0 developed in VS2010 on my workstation that is in a domain called 'DOMDEV' trying to access a MS-SQL Server 2000 DE on a standalone W2K3 Std Edition SP2. My domain credentials are

user: DOMDEV\MyName pwd: p@ssword

The MS-SQL Server 2K DE's security is set to Windows authentication mode and CANNOT be changed to mix mode due to the company regulations.

My connection string looks like the following in the aspx's web.config file: Data Source=standAlone.myCompany.com\SQL01;Initial Catalog=db_001;Integrated Security=SSPI;

When I do a Ctrl-F5 in VS2010 on my workstation, the page executed well and pulled all data from the standalone server.

When I published the page to a Window Server 2K8 Enterprise and try to request the page through my IE 9, I got the error below: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Why does it work on my set without setting anything but not on the W2K8? What is the security info sent from my VS IIS different from the W2K8's IIS7? The web site on W2K8 IIS7 is set to Windows Authentication enabled Anonymous Authentication enabled: Application Pool Identity

How/what can I do to configure the W2K8 to let IIS 7 works the way like the IIS on my set?

Thank you.

Answer 1

I played around with the IIS 7 setting and found the following configuration worked out well.

Part I: Create a local user on the database server

1. open Start > Administrative Tools > Computer Management
2. expend Computer Management (Local) > Local Users and Groups > Users
3. add a new user with the name you like (e.g. yourNewUser; pwd: 987poi!@)
4. add the new user in the local (BUILD-IN) Administrator group
5. nothing need to be set on the DB who is set to Windows Authentication mode

Part II: Create a local user on the web server

1. open Start > Administrative Tools > Server Manager
2. expend the left navigation Server Manager (yourServerName) > Configuration > Local Users and Groups > Users
3. add a new user with indentical information that you just did on the database server
4. no extra setting/permission/groups needed (minimum rights). By default it is in 'Users' only

Part III: Add permission to the .NET temp folder

1. 32-bit: go to your X:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files folder, ensure the new user have write permission to the folder
2. 64-bit: go to your X:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files folder, ensure the new user have write permission to the folder

Part IV: Option 1: Configure the web server

1. open Start > Administrative Tools > Server Manager
2. expend the left navigation Server Manager (yourServerName) > Roles > Web Server (IIS) > Internet Information Services (IIS) Manager
3. in the navigation (middle left) of the Internet Information Services (IIS) Manager, click on your web application
4. in IIS section, double click 'Authentication' icon

5. there are 6 settings in the Authenticaiton pane. By clicking on the name of the settings you will be able to enable/disable/edit them in the Action pane at the right. Set them as the following:

   • Anonymous Authentication - Enabled - Application Pool Identity
   • ASP.NET Impersonation - Enabled - Specific user: yourNewUser (987poi!@)
   • Disable the rest of them
6. every time you publish the web app, you will need double check if the setting is still there

Part IV: Option 2: Setup web.config file

1. go to your web.config file of the web app you are publishing, ensure you have the following line in your system.web block

   < identity impersonate="true" password="987poi!@" userName="yourNewUser" />

2. this option doesn't work on the local workstation's IIS (at least it won't work on mine.)

Now request from a user's workstation, the page will come up with the data needed.

This solution also works on Windows Server 2003 w/ IIS6 with .net4