1

I'm getting the following error when trying to query SQL Server 2012 from Windows 2012 IIS 8.5, in an MVC 4 website:

SqlException (0x80131904): Login failed for user [domain/user]

I have the connection string in Web.config set up just the way we did it back in the olden days:

<add name="MYPROJECT.Properties.Settings.defaultConn" connectionString="Data Source=MYSERVER;Initial Catalog=MYDATABASE;Persist Security Info=True;User ID=MYUSER;Password=MYPASSWORD"

IIS8 seems to be using an Application Pool to login, bypassing the connectionstring in Web.config.

Database and Website are on different servers. Is there a straightforward way that I can configure IIS to bypass the Application Pool and read the connectionstring from Web.config?

j_d_b
  • 638
  • 5
  • 11
  • Are you sure that's the connection string that's failing? – John Saunders Aug 18 '14 at 19:36
  • No the connection string is being entirely ignored. the [domain/user] that shows up in the error is the user configured in IIS for the application pool. I'm wonderinf how to get IIS to bypass the application pool and read the connection string from Web.config. – j_d_b Aug 18 '14 at 19:49
  • IIS isn't doing anything here. It doesn't know anything about connection strings or SQL Server. You're simply not using the connection string you think you're using. – John Saunders Aug 18 '14 at 19:50
  • Yes, that's really the question. IIS is simply ignoring the connection string in web.config, and instead trying to authenticate via the application pool. I'm hoping to bypass the application pool and get it to read the connection string in web.config. – j_d_b Aug 18 '14 at 19:57

1 Answers1

0

If Persist security info is set to true, it will be ignored. From MSDN

The ConnectionString is similar to an OLE DB connection string, but is not identical. Unlike OLE DB or ADO, the connection string that is returned is the same as the user-set ConnectionString, minus security information if the Persist Security Info value is set to false (default). The .NET Framework Data Provider for SQL Server does not persist or return the password in a connection string unless you set Persist Security Info to true.

Martin Folkeseth
  • 317
  • 1
  • 11
  • I had "Persist Security Info=True;" which worked when I was developing locally. I just switched it to "Persist Security Info=False;" and am getting the same error. The user in the web.config is a sql server user while the user in the error is a domain user from the application pool. Not sure which setting I should be using, but they both are returning the same error at present. – j_d_b Aug 18 '14 at 19:55
  • A stupid question maybe, but have you set the SQL server to accept SQL Server Authentication? If this is not the case, I guess it will try to access the database using the app pool user. – Martin Folkeseth Aug 18 '14 at 20:14
  • SQL Server is set up for mixed authentication. The web.config connection string works when I'm developing locally, it's just being ignored by the Windows 2012 Server IIS 8. Instead it's trying to authenticate via the application pool user account. – j_d_b Aug 19 '14 at 14:18
  • You are running MS SQL 2012 and IIS 8.5 both locally and production? Please update your post to describe your environment in more detail. Help us help you. – Martin Folkeseth Aug 19 '14 at 16:25
  • Locally I'm running .NET 2012 on Windows 7. I connect the same 2012 SQL Server over the network, in both environments. In production, I'm installing the MVC project on a 2012 Windows Server running IIS 8. Everything works as expected locally. However, in production, IIS 8 is connecting to SQL Server via the application pool, trying to connect via a Windows account (DOMAIN.USER$). It's ignoring the Web.config entirely. So the question is, how do you configure IIS 8 to connect using SQL Server Authentication. – j_d_b Aug 19 '14 at 20:10
  • Are you absolutely sure the production environment is able to access your SQL server? Try opening [Management Studio](http://www.microsoft.com/en-us/download/details.aspx?id=8961) and connect manually. If you are able to connect, check out [this post](http://stackoverflow.com/questions/2806438/login-failed-for-user-domain-machinename). Seems to be the same problem as the one you are having. – Martin Folkeseth Aug 19 '14 at 21:48
  • I think the reply in that thread that discusses creating a custom application pool is the right idea. In this case the answer lies in setting up the application pool to use the SQL Authentication specified in the web.config connectionstring. – j_d_b Aug 20 '14 at 01:14
  • Alright, but are you able to connect to the SQL server from the production environment manually? – Martin Folkeseth Aug 20 '14 at 04:46
  • Yes, of course, Martin. I appreciate your attention to this thread, but this conversation has nothing to do with the question asked. I am wondering how to configure IIS8 to do something specific. This line of questioning has nothing to do with IIS8 configurations. – j_d_b Aug 20 '14 at 15:01