Sql-server – New SQL Server from backup; application has successful login on 1 database, but fails on another: 0x80040e4d

connectivitydisaster recoverysql serversql-server-express

Our SQL Server crashed and burned, the hard drives destroyed. No hope of recovery.

We need to get a very important application working again; this is on a different server.

  1. We've set up a new SQL Server using SQL Server Express 2014 and attached the .MDF files from the most recent backup.

  2. We created a user and gave access to all the databases.

  3. We changed the connection string in the application to use the new SQL Server and the new user. Connection string:
    Provider=sqloledb;Data Source=<IP-address>;Network Library=DBMSSOCN;Initial Catalog=<DB>;User ID=<UID>;Password=<PWD>;

  4. The application successfully connects and reads data from one of the databases, but fails with error: "Login failed for user 'goa'." on another database.

  5. The SQL Server log files shows the following:
    Login failed for user 'goa'. Reason: Password did not match that for the login provided. [CLIENT: <IP Address>] and Error: 18456, Severity: 14, State: 8.

The only difference between the connection string that succeeds and the one that fails is the database.

The user does have access to both databases, and we are able to log in with that user in SQL Server Management Studio and do anything with either of the databases. Both databases are on the same SQL Server instance.

What puzzles me is that it claims wrong password when the same password worked fine less than a second before, when it connected to the other database.

Does this have anything to do with the limitations of SQL Server Express?

Application log file

Microsoft OLE DB Provider for SQL Server  
Description = Login failed for user 'goa'.  
HelpFileContext = 0  
Error 0  
Number = 0x80040e4d  
Source = Microsoft OLE DB Provider for SQL Server  
Description = Login failed for user 'goa'.  
HelpFileContext = 0  
SQL State = 42000  
NativeError = 18456

Some attempts

  • We've tried changing the order of the connections, so that database B goes first, but it still fails at B; so it is NOT a matter of a limit to the number or frequency of connections.

  • We've made sure the user has access to both databases under *User Mapping* and by right clicking the databases themselves, going to Properties >> Permissions, and granting everything. It still can't connect.

  • We've tried changing the passwords: in which case neither connection worked. This gives me the impression that there is some encryption problem(?), and that the SQL Server tries to decrypt the password with the wrong key or something, and by chance uses the correct key in certain circumstances.

  • Adding Integrated Security=SSPI;, to the connection strings, did not help. Nor did Integrated Security=yes; or Trusted_connection=yes;.

Best Answer

Here is a summarization of our efforts to make this work:

  • We set up a new SQL Server using SQL Server Express 2014 and attached the .MDF files from the most recent backup.

  • We created a user and gave access to all the databases; by giving ownership and db_datareader and db_datawriter to the user for all the relevant databases, and by granting rights under User Mapping.

  • In the application we needed to get back online: The initial connection string, that worked fine with the old SQL server before it crashed, was: DSN=<SQL_Server>;UID=<User Id>;PWD=<Password>;Database=<Catalogue>;

    • Our initial attempt was to replace <SQL_Server> with the IP-address to the new SQL Server (with and without port).
    • This did not work; so we changed DNS=.. to Server= (with and without port); this did not work either.
    • All these attempts gave errors that the connection string was invalid. So we looked up different connection strings for IP-Addresses using OLE DB and found this: Provider=sqloledb;Data Source=<IP-address>;Network Library=DBMSSOCN;Initial Catalog=<Database>;User ID=<User>;Password=<Password>;. This worked, but only for one of the connections, for database A, while database B still failed.

  • The application successfully connects and reads data from database A, but fails with error: "Login failed for user 'goa'." on database B.

    • The SQL Server log files shows the following: Login failed for user 'goa'. Reason: Password did not match that for the login provided. [CLIENT: <IP Address>] and Error: 18456, Severity: 14, State: 8.

  • We tried changing the passwords, and then neither connection worked. This gives me the impression that there were some encryption problem (because I recalled a mention of encryption in one of the forums I had browsed, looking for answers), and that the SQL Server tried to decrypt the password with the wrong key or something, and by chance uses the correct key in certain circumstances.

    • To solve this potential encryption problem; we added Integrated Security=SSPI; to the connection strings, this did not help. Nor did Integrated Security=yes; or Trusted_connection=yes;.

  • To make sure this was not a matter of the number of connections, or a limit in the number of open connections at a time: we changed the order of the connections so that database B went first, and A went second; and this time it was still the database B that failed.

  • We tried ressurecting the users from the original SQL server, by creating those users and executing: EXEC sp_change_users_login 'Auto_Fix', '<user>', NULL, '<Password>'. This gave a positive result: The number of orphaned users fixed by adding a new logins and then updating users was 1.. Thanks to @SQLPRODDBA and @Chad-Mattox

This is what worked for us:

  • Changing the connection string to: Driver={SQL Server};Server=<IP-Address>;UID=<User Id>;PWD=<Password>;Database=<Catalogue>;That is going back to the initial format and adding Driver=.. to the string. For this connection we did not use a ressurected user from the old SQL server.
Related Question