SQL Server 2012 migration and SA security access
I am moving our SQL server 2008 database to a new server running SQL server 2012. I have basically backup the old instance and restored it to the new server. The old instance used the sa login account. I do not have the sa security account login from the old server available. When I try and log on to the new server using sa account, I get the following error.
Cannot connect to SERVER3.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
I have tried to change the password of the SA account through management studio logged in under windows authentication, but still no luck.
What can I do to can access with the SA account on the new server?
Cannot connect to SERVER3.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
I have tried to change the password of the SA account through management studio logged in under windows authentication, but still no luck.
What can I do to can access with the SA account on the new server?
ASKER
Yes, only user database was backed up and restored to new.
I changed the SA password on the new server from studio management by right clicking on on sa account-security-propertie
That may be the issue? I was trying to guess what the password was and I thought I would need to have the same sa credentials from the old to be used on the new.
The new servers is not in production yet, just a test server.
I changed the SA password on the new server from studio management by right clicking on on sa account-security-propertie
That may be the issue? I was trying to guess what the password was and I thought I would need to have the same sa credentials from the old to be used on the new.
The new servers is not in production yet, just a test server.
No matter how you changed it it must allow you to connect with SA SQL login but what user did you used to change it? You should be able to use the same user and change it again or to see if its not disabled.
And hopefully there's some other account (NT or SQL login does not matter) having sysadmin rights. You may want to look at this just to make sure if you disable SA you don't get completely locked out that SQL server as sysadmin: http://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/
ASKER
I can log in with studio manager with windows authentication as administrator which has sysadmin rights and I have full access to the database. trying to login the database on the client side I get the following in the server events log:
"An attempt to login using SQL authentication failed. Server is configured for Windows authentication only"
When I researched the error, I find articles stating to change the security authentication method on the server to mixed mode will correct, however there is no option there to change it.
Screenshot-2015-11-20-12.32.55.png
"An attempt to login using SQL authentication failed. Server is configured for Windows authentication only"
When I researched the error, I find articles stating to change the security authentication method on the server to mixed mode will correct, however there is no option there to change it.
Screenshot-2015-11-20-12.32.55.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found the solution
"When I try and log on to the new server using sa account, I get the following error." - but you know the SA password from the new server right? So from where and how "... I try and log on to the new server using sa account..."?