Migrating from SQL 2008 to SQL 2012 but cannot connect to the database

Question

We are now migrating from a SQL 2008 to a SQL 2012 server. I am testing the databases one at a time as these are live databases.

I have backed the database up and then restored this onto the new server. Created the associated security accounts needed to access this.

My username is part of a security group. It does have db_owner rights as well as public as this is what was on the old server.

When I try and connect using ODBC using Windows Authentication, i get (Login failed for the user "my username") The server is set to mixed mode and when I looked at the log files, there is this message that is shown Login failed for user 'Username'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: x.x.x.x] IP Address

I did some coding on the server, and noticed an error with regards to the TCP not being enabled which I have done. I have also disabled UAC but I still cannot connect to the database.

I can connect using a local admin account that was created but I need this to be connected using domain accounts as all the staff use this database.

Please if anyone has some suggestions to this, then PLEASE HELP!!!!

Thanks Martin

PS - I am not a massive SQL developer but I can understand the simple codes etc

Answer 1

You wrote that you create the required user accounts.

As in AD also on sql server the user accounts have a sid that is unique; creating a user with the same name does create a different user.

When you restore a database on a different server there are 2 options: script users (with sid and all the background stuff) on the origin server and recreate them on the new server or script the permission and reapply them on the destination server.