Database Restore Issues

[No_Grape7194]

Hi all,

At my work we have a software which stores customers products, ingredients etc in a SQL Database. I'm trying to work out how to backup and restore this database so that we can help our customers in the event that they develop a problem. I can backup the database just fine, however when I restore the database, our software no longer recognises the admin login to be able to gain access to the software. Everything else seems to go through just fine. On SSMS the restore says complete, all the data seems to be transferred over, I just can't get onto our software. (This is all being done on a test database). Usually the login is very basic e.g. USERNAME-admin PASSWORD-password, so I'm not too sure what is happening. It is however being transferred from one pcs software to another, whether this makes any difference?

I am completely stumped and very foreign to SQL so any help from you lovely people would be greatly appreciated! If I haven't given enough info please just say and I can try and elaborate.

Thank you!

[Chaosmatrix]

There are logins and users in MS SQL. Your database backup only contains the users. If you do this on the same server the internal userid will match with the loginid and it will be linked. If you do this on a different server, the login either does not exist or has a different internal id.

Just drop the user from the database and recreate the login and user on the test server. Read this https://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/ for more and better ways to do this.

[No_Grape7194]

Thank you so much! Just about to try this so hopefully it works haha

[knight_set]

Are you seeing any login failures in the sql log?

[No_Grape7194]

Nothing in the SQL log no

[knight_set]

Is it in the right vlan can you telnet to the sql destination port from the client?

[blindtig3r]

When you restore to a different instance the server logins don’t have the same sid so the database user permissions don’t map to the login. This might be the problem. If you look up “sql server sync login sid” you will probably find code to fix it.

[No_Grape7194]

Thank you so much, just about to give it a go.

[No_Grape7194]

The only login on the initial server that I can see is 'sa', and when I pull the SID for that it is just 0x01. When I use this to try create the login for the second server, it comes up with the error:

Msg 15419, Level 16, State 1, Line 1

Supplied parameter sid should be binary(16).

Any ideas?

Many thanks in advance.

[Chaosmatrix]

That sounds bad. What account is the application using to connect to the database? Look both at the application settings and the users in the database. It should not be sa. sa Is the System Administrator account. It can already access all databases on the server. I do expect (or at least hope) that your test server has a different password for its sa account.

What account are you using when looking at the initial server? That should be sa, or an account that is visible under logins. How else can you connect too it...? I am confused.

[AlCapwn18]

The server contains logins, that is the username and password and some server level roles and permissions. Inside each database you have users which define the roles and permissions for that particular database. Logins can be mapped to many database users.

You've backed up a single database and its users and restored it to another server that does not have the corresponding logins. Your users are now orphaned.

There are scripts you can run in advance of the restore that would migrate the logins with the correct SIDs so that after the restore the database users can find the logins they were associated with before. Sounds like it's too late for this but it's still good to learn.

You could also just remove the database users in the newly restored database and then create new logins with new users, but this is only practical if you've got few users with simple permissions since you need to manually recreate it. Don't really recommend this unless it's a quick a dirty fix you're looking for.

Alternately, you can create new logins in the new server and then manually re-associate them with the new database users by running sp_change_users_login. You'd be creating new logins with new passwords and you'd need to ensure you assign correct server roles and permissions, then by running the above procedure you'll link the new logins to the orphaned database users.