Menu
When I want to connect to SQL Server 2008, I get this message:
Cannot connect to server.
Additional Information: Cannot open user default database. Login failed. Login fail for user 'sa'. (Microsoft SQL Server. Error:4064)
How do I resolve this error?
579
Check out Windows or Mixed authentication mode is enabled. For that, select the database, right click on it, and choose “Properties”. In the Security tab, check out the server authentication section. If the authentication mode is changed, then do not forget to restart SQL Server to allow changes to take place.
Right click on the database server and go to properties. Choose the security option and check "SQL Server and Windows authentication mode". Enable TCP/IP connection in SQL Configuration Manager. Restart your SQL server.
User logins can fail for many reasons, such as invalid credentials, password expiration, and enabling the wrong authentication mode. In many cases, error codes include descriptions. The following examples are some of the common login failures. Select the exact error that you're experiencing to troubleshoot the issue.
This error (Microsoft SQL Server.Error:4064) occurs when the database which is dropped is default for some database user.
When the user tries to login and their default database is dropped the error shows up.
Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)
The way to resolve this is given here, summarized below.
The solution to log on is:
The permanent solution to fix the server is to run SQL like:
ALTER LOGIN [test] WITH DEFAULT_DATABASE = master GO In my case this was due to a user being a member of multiple groups, each group having its own login. All groups' logins need to have their default database set to a database accessible to the user.
Other possible problems are listed at https://support.microsoft.com/en-us/kb/307864.
You can set each login with an invalid default database to have a default database of master (accessible to all logins):
alter login [loginname] with DEFAULT_DATABASE = master If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
