After rename a database - cannot connect as administrator
Question
Saturday, March 17, 2012 5:27 PM
Wanted to install new data from a customer database into my devt system which has same set of three databases all running SQL Server 2008 R2 on a Windows Server 2008 R2 server machine.
When I renamed one of the databases - then I cannot any longer connect to SQL server as administrator. I get SQL error 4064 at the Connect screen into SQL Server Management Studio.
Thanks for any help or pointing me in the right direction to resolve this.
All replies (7)
Monday, March 19, 2012 7:31 AM ✅Answered
Hi RLB Associates,
Thank you for your update, I am so glad to hear that you have solved your issue by rename your database to original name. Regarding to the error number 4064, which is related to the user default database is unavailable at the time of connection.Did you rename the default database? If so, it is possible that the database is no longer exists or in suspect mode.
Additionally, the login account may be a member of multiple groups and the default database for one of those groups is unavailable at the time of connection.
There is a workaround to avoid the error when the user's default database is unavailable is to log on as a user who can modify logins. Then, change the user's default database to a database that is currently available for a connection.
As far as being unable to connect using ‘sa’ credentials, verify that you have mixed mode authentication enabled. If you do not that might explain why you are getting trusted connection error for ‘sa’.
For more information, please refer to the workaround section in this KB and replies in this thread.
Regards,
Amber zhang
TechNet Subscriber Support
If you are TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.
Saturday, March 17, 2012 5:53 PM | 1 vote
It looks like your login's default database was the renamed one. When you connect from SSMS, override the default database by clicking the Options button and specifying master as the database name. Then change the default database with "ALTER LOGIN <your_login> WITH DEFAULT_DATABASE = master";
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
Saturday, March 17, 2012 7:38 PM
This does not work for me because attempts to ALTER LOGIN do not work.
If I follow your suggested rule exactly, I get the same 4064 error as before.
If I use sqlcmd to alter login, I get a connection error. If I try to exec using the sqlservr command it cannot find the command.
If I enter the above into the Additional Connection Parameters under the Options button, I get error "Format of the initialization string does not conform to specification starting at index 196".
With only a change to the default database to 'master'. I get the same error as above but at index 178.
Saturday, March 17, 2012 8:23 PM
This does not work for me because attempts to ALTER LOGIN do not work.
If I follow your suggested rule exactly, I get the same 4064 error as before.
If I use sqlcmd to alter login, I get a connection error. If I try to exec using the sqlservr command it cannot find the command.
If I enter the above into the Additional Connection Parameters under the Options button, I get error "Format of the initialization string does not conform to specification starting at index 196".
With only a change to the default database to 'master'. I get the same error as above but at index 178.
sqlservr.exe is the database engine itself so you don't need to run that for this task. What is the exact error you get with the following command?
SQLCMD -S YourSqlServer -E -d master
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
Saturday, March 17, 2012 8:41 PM
It looks like your login's default database was the renamed one. When you connect from SSMS, override the default database by clicking the Options button and specifying master as the database name. Then change the default database with "ALTER LOGIN <your_login> WITH DEFAULT_DATABASE = master";
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
start SQL Server in single user mode and change the default database for your login.
see http://msdn.microsoft.com/en-us/library/ms180965.aspx how to start sqlserver in single user mode.
I would not recommand to set default database do a non system database for a sysadmin account to avoid such problems.
Saturday, March 17, 2012 8:44 PM
I get the following
HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or is inaccessible. Check if instance name is incorrect and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Sqlcmd: Error: Microsoft SQL Server native Client 10.0 : Login timeout expired.
Rich Balluff
RLB Associates
Saturday, March 17, 2012 10:50 PM
I noticed that in spite of the 4064 error at connect time, the list of databases was accessible and so I simply selected the database I had originally renamed and "Renamed" it back again to the original name and then all appears to be well. I do not know why - BUT?! there it is!
Thanks to Dan Guzeman and Daniel Steiner for your support.
RLB Associates