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.
We've 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.
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>;
The application successfully connects and reads data from one of the databases, but fails with error:
"Login failed for user 'goa'."on another database.
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
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.
Integrated Security=SSPI;, to the connection strings, did not help. Nor did