Connection failed to SQL Server Express using user with SQL Server authentication

Question

I have IIS and SQL Server Express on the same machine. After deployment of web site to this environment I get this error:

Cannot open database "MyDB" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\USER'.

Here are my connection strings where I set SQL Server user

<connectionStrings>
    <add name="myCS1" 
         connectionString="data source=172.20.3.20\SQLEXPRESS;initial catalog=MyDB;user id=SomeUser;password=SomeUser###;integrated security=True;MultipleActiveResultSets=True;" 
         providerName="System.Data.SqlClient" />
    <add name="myCS2" 
         connectionString="metadata=res://*/CLModel.csdl|res://*/CLModel.ssdl|res://*/CLModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=172.20.3.20\SQLEXPRESS;initial catalog=myDB;persist security info=True;user id=SomeUser;password=SomeUser###;MultipleActiveResultSets=True;App=EntityFramework&quot;" 
         providerName="System.Data.EntityClient" />
</connectionStrings>

I want to use SQL Server user for connection to the database but not AppPool user.

I specified login and password but it seems that this info is not taken in account and in error message I still see IIS user but not sql.

What I missed?

marc_s · Accepted Answer · 2015-02-26 08:41:27Z

Well, you specify both - explicit user id and password, and Integrated Security=True - in your myCS1 connection string:

data source=172.20.3.20\SQLEXPRESS;initial catalog=MyDB;
  user id=SomeUser;password=SomeUser###;integrated security=True;

In this case, the integrated security wins over your user - you need to specify only the user id and password and get rid of the integrated security - so use this:

<connectionStrings>
    <add name="myCS1" 
         connectionString="data source=172.20.3.20\SQLEXPRESS;initial catalog=MyDB;user id=SomeUser;password=SomeUser###;MultipleActiveResultSets=True;" 
         providerName="System.Data.SqlClient" />
    <add name="myCS2" 
         connectionString="metadata=res://*/CLModel.csdl|res://*/CLModel.ssdl|res://*/CLModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=172.20.3.20\SQLEXPRESS;initial catalog=myDB;persist security info=True;user id=SomeUser;password=SomeUser###;MultipleActiveResultSets=True;App=EntityFramework&quot;" 
         providerName="System.Data.EntityClient" />
</connectionStrings>

Collectives™ on Stack Overflow

Connection failed to SQL Server Express using user with SQL Server authentication

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related