Cannot Connect to MS SQL Server 2005 Instance with Windows Authentication on Windows Vista RC1
I've installed an instance of Microsoft SQL Server 2005 on Microsoft Windows Vista RC1 Ultimate Edition a few days ago. I setup the sql server2005 instance with a name "KODYAZ-PC\SQL2005" and configured a "sa" password in order to have a server authentication "SQL Server and Windows Authentication mode"
I feel lucky when I opened the Microsoft SQL Server Management Studio in order to connect to the newly installed instance. I just selected the Windows Authentication mode for connection. But I got the "Cannot connect to ..." error message. I placed a screenshot of the error below.
When I checked the error messages for this problem, I found two definitions which both are nearly the same. One of the error text also indicates the error number Error: 18456
------------------------------
Cannot connect to KODYAZ-PC\SQL2005.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'Kodyaz-PC\eralper'. (Microsoft SQL Server, Error: 18456)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
The other message is a little bit more detailed as I copied down below.
===================================
Cannot connect to KODYAZ-PC\SQL2005.
===================================
Login failed for user 'Kodyaz-PC\eralper'. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
------------------------------
Server Name: KODYAZ-PC\SQL2005
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire,
SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host,
String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject,
SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean
redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo
ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
Actually I was sure that this error is a result of the User Account Control system of the Windows Vista which is always confirming for using administrative permission or rights in order to continue a task which requires administrative privileges.
I could not connect to the SQL Server 2005 on my Windows Vista with my Windows privileges but I changed the Authentication mode to SQL Server Authentication and supply the "sa" password to connect to the sql server successfully.
Right after I loggen on to the SQL Server, although the BUILTIN\Administrators user group exists in the Logins section, I added my user "eralper" which is the default user in the Vista RC1 installation as a new login account as shown below in the following picture.
I grant the sysadmin role to the new login account, which is the default Windows Vista user account.
After I have added the Vista user account as a new login and given the sysadmin role to this account I could successfully logon to the new SQL Server 2005 instance on the Windows Vista RC1.