I'm migrating the database for a .NET application from SQL Server 2008 R2 to SQL Server 2014. Application was built using VS 2015 and VB.
The application runs over our intranet, using Windows Authentication and an AD group with all the users who have access to the application. I have a single user set up in SQL Server, who has access to all the required database objects.
Currently, application runs under IIS 8.0.9 on one Windows 2012 virtual server. The database is located on another virtual server running Windows 2008R2 and SQL Server 2008R2. This set up runs perfectly and I've never had connection issues.
I can run the application fine from Visual Studio 2015/SQL Server 2014, and the login works fine so I know there is no issue with authentication to SQL Server using the credentials supplied in the web.config file. I can also login to SSMS 2014 with the same
credentials, so there no issue with a wrong password or username.
However, in testing the application on the IIS Server (i.e I select 'browse' to open browser session), and entering what I know are the correct AD credentials, I get the message 'Login failed for 'MyDatabaseUser'. If I try to run the app from my browser
I get 'System.Data.SqlClient.SqlException: Login failed for user 'MyDatabaseUser'.
This only happens on the server running SQL Server 2014. I have several applications running successfully with this configuration on SQL server 2008, so I'm stumped as to what is causing this issue and nothing I tried has worked.
<ed>I would like to add the at all logins fail, even for sa.</ed>
So your db connection is using a SQL Server account rather than "integrated security" ? Could it be a new server for which SQL Server authentication is not yet enabled ?
If you need further help, don't you have some additional text that follows this error ? The best starting point is the exact and full error message (or the best English translation you can).
Thanks for the reply Patrice. I feel puzzled and a little embarrassed. Let me explain.
I've been testing the development SQL 2014 Server, with which I was experiencing all the issues I described. On a hunch I decided to restore the database onto the
production SQL 2014 Server, make changes to the connection string of my IIS application, make sure the database user has all the correct permissions etc etc, and test there to see if this also failed. It worked like a charm! I'm now able to login to
the application via a browser using my AD credentials!
One another hunch this morning, I changed the connection string in my IIS web app to point to back the
development server, and this is now also working when it wasn't before!! What's going on? I've made no structural or design changes to anything other than a connection string. Is this (instability) going to keep happening?
I will speak to sys admins to see if he's been messing around in the wee small hours but I have to say I'm a worried.
As you can see in the earlier link you have multiple messages starting with the same message and then additional text each with disctinct causes. This is why it is IMO important to start from the exact and full error message.
For now it seems you just changed your connection string ? Double check what you changed? Do you run into the same error if reverting back your connection string change ?
Edit: also you are talking about AD authentication to the site which is not directly related (my understanding is that you have anyway another account - still a bit unclear it this is a SQL Server or Windows account - to connect to SQL Server)
Sorry for the confusion. The site uses AD Authentication. Access to the SQL Server database is through a SQL Server user, and its login credentials are defined in the application's web.config file.
When I migrated the database from Windows 2008\SQL Server 2008 R2 onto Windows 2012\SQL Server 2014, I naturally had to change the connection string to point to the 2014 SQL Server instance. This is where the connection problems started. Luckily, these
appear to have been resolved, although I'm not certain what the resolution was.
Member
15 Points
183 Posts
Login fails under IIS
Jun 26, 2018 09:34 AM|mojo99|LINK
Hello,
I'm migrating the database for a .NET application from SQL Server 2008 R2 to SQL Server 2014. Application was built using VS 2015 and VB.
The application runs over our intranet, using Windows Authentication and an AD group with all the users who have access to the application. I have a single user set up in SQL Server, who has access to all the required database objects.
Currently, application runs under IIS 8.0.9 on one Windows 2012 virtual server. The database is located on another virtual server running Windows 2008R2 and SQL Server 2008R2. This set up runs perfectly and I've never had connection issues.
I can run the application fine from Visual Studio 2015/SQL Server 2014, and the login works fine so I know there is no issue with authentication to SQL Server using the credentials supplied in the web.config file. I can also login to SSMS 2014 with the same credentials, so there no issue with a wrong password or username.
However, in testing the application on the IIS Server (i.e I select 'browse' to open browser session), and entering what I know are the correct AD credentials, I get the message 'Login failed for 'MyDatabaseUser'. If I try to run the app from my browser I get ' System.Data.SqlClient.SqlException: Login failed for user 'MyDatabaseUser'.
This only happens on the server running SQL Server 2014. I have several applications running successfully with this configuration on SQL server 2008, so I'm stumped as to what is causing this issue and nothing I tried has worked.
<ed>I would like to add the at all logins fail, even for sa.</ed>
Can anyone help?
TIA.
All-Star
48530 Points
18073 Posts
Re: Login fails under IIS
Jun 26, 2018 02:53 PM|PatriceSc|LINK
Hi,
So your db connection is using a SQL Server account rather than "integrated security" ? Could it be a new server for which SQL Server authentication is not yet enabled ?
Try perhaps https://support.microsoft.com/en-us/help/555332/login-failed-for-user-error-message-when-you-log-on-to-sql-server
If you need further help, don't you have some additional text that follows this error ? The best starting point is the exact and full error message (or the best English translation you can).
Member
15 Points
183 Posts
Re: Login fails under IIS
Jun 27, 2018 08:35 AM|mojo99|LINK
Thanks for the reply Patrice. I feel puzzled and a little embarrassed. Let me explain.
I've been testing the development SQL 2014 Server, with which I was experiencing all the issues I described. On a hunch I decided to restore the database onto the production SQL 2014 Server, make changes to the connection string of my IIS application, make sure the database user has all the correct permissions etc etc, and test there to see if this also failed. It worked like a charm! I'm now able to login to the application via a browser using my AD credentials!
One another hunch this morning, I changed the connection string in my IIS web app to point to back the development server, and this is now also working when it wasn't before!! What's going on? I've made no structural or design changes to anything other than a connection string. Is this (instability) going to keep happening?
I will speak to sys admins to see if he's been messing around in the wee small hours but I have to say I'm a worried.
Anyone have a clue what's going on?
All-Star
48530 Points
18073 Posts
Re: Login fails under IIS
Jun 27, 2018 09:31 AM|PatriceSc|LINK
As you can see in the earlier link you have multiple messages starting with the same message and then additional text each with disctinct causes. This is why it is IMO important to start from the exact and full error message.
For now it seems you just changed your connection string ? Double check what you changed? Do you run into the same error if reverting back your connection string change ?
Edit: also you are talking about AD authentication to the site which is not directly related (my understanding is that you have anyway another account - still a bit unclear it this is a SQL Server or Windows account - to connect to SQL Server)
Member
15 Points
183 Posts
Re: Login fails under IIS
Jun 27, 2018 10:12 AM|mojo99|LINK
Sorry for the confusion. The site uses AD Authentication. Access to the SQL Server database is through a SQL Server user, and its login credentials are defined in the application's web.config file.
When I migrated the database from Windows 2008\SQL Server 2008 R2 onto Windows 2012\SQL Server 2014, I naturally had to change the connection string to point to the 2014 SQL Server instance. This is where the connection problems started. Luckily, these appear to have been resolved, although I'm not certain what the resolution was.
All-Star
48530 Points
18073 Posts
Re: Login fails under IIS
Jun 27, 2018 10:17 AM|PatriceSc|LINK
When using a SQL Server account you may have to do something such as https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-change-users-login-transact-sql?view=sql-server-2017 (about matching the SQL Server database user and the SQL Server login)
(but I'm not sure which error is shown in this case)
Member
15 Points
183 Posts
Re: Login fails under IIS
Jun 27, 2018 10:19 AM|mojo99|LINK
Thank you for your help.