locked
Login fails under IIS RRS feed

  • Question

  • User411346422 posted

    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.

    Tuesday, June 26, 2018 9:34 AM

All replies

  • User753101303 posted

    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).

    Tuesday, June 26, 2018 2:53 PM
  • User411346422 posted

    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?

    Wednesday, June 27, 2018 8:35 AM
  • User753101303 posted

    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)

    Wednesday, June 27, 2018 9:31 AM
  • User411346422 posted

    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.

    Wednesday, June 27, 2018 10:12 AM
  • User753101303 posted

    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)

    Wednesday, June 27, 2018 10:17 AM
  • User411346422 posted

    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)

    Thank you for your help.

    Wednesday, June 27, 2018 10:19 AM