Avatar of Justin Blanchard
Justin Blanchard
Flag for United States of America asked on

Error logging into SQL server from my program: Could not find a login matching the name provided. [CLIENT:]18456, Severity: 14, State: 5.

On a Server 2008 R2 install sits a Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)  that was kept running only to access previous medical records as the company migrated to new software and servers, but medical records need to be kept for 7 years.  on 2/15/21 I see this in the log file, Running SQL Server 2005 SP2 to SQL Server 2008 upgrade script, and a large series of events.  Fast-forward a few months and an Audit pops up plus a product recall and the old database and program need to be accessed. Now when a user attempts to log into the program the user gets rejected for not being a user and the log records the error above.
The original software company wants extortion money to get their old software up and running and I have been tasked with trying to get the software going. Can someone help me get around this error?
I'm thinking permissions and or groups... I am NOT an expert in any of this!!

Microsoft SQL Server

Avatar of undefined
Last Comment
Justin Blanchard

8/22/2022 - Mon

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Andrei Fomitchev

1. Run SSMS on the server where SQL 2005/2008 R2 installed. Try to connect to the database. If success then the issue is in Network or on the client machine.

2. If (1) is successful: Run SSMS on the client computer. Try to connect to SQL Server. If successful then the issue is with the application. If fails try to install the SQL Client for SQL 2008 R2 on the client's computer.

3. Try to setup compatibility level on SQL Server to SQL 2005. If it does not help then the issue is in the Application.

4. Create ODBC connection to SQL Server. Try to use ODBC connection in your application. Try different versions of MS SQL Server drivers for ODBC.

5. As far as I remember between SQL 2005 and 2008, the ADO access was replaced with OLE access. It could be the reason of the connection failure. It could be the thing that the vendor should change in the application. In this case you have to pay the price.
Justin Blanchard

So...I have progress... and I got a user in, but I don't know how I did it.
I did a few things.

I found that I have orphans because of the restore...
I used both:
sp_change_users_login 'AUTO_FIX', 'someuser'

Open in new window

EXEC sp_change_users_login 'Auto_Fix', 'myUser'

Open in new window

I don't know which one did it.

I do notice that some users are in the Database /security / users
and some are in security / log ins below.  

I tried so much stuff i don't know what did it, but now i need to repeat it.

i used this:
Use [YourDbName] ;
exec sp_change_users_login @Action='Report' ;
 which gets me a big list of users, the user i have working is not on this list...

now what?...
I tried the 2 sp_change_users_login... but I'm not getting results...
delete and re-add perhaps?

Justin Blanchard

I wound up finding orphans and repairing those who would repair.
Others I needed to remove entirely from each database, then add back in along with add them to the sql security.

Great tips by both folks pointed me to my solutions.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes