I installed a new sql 2008 in a vmware server that was 'not' an AD (but I did join the domain with it). I set it up as mixed mode, and gave it an 'sa' password (at least I assume that user is still 'sa', been awhile since I used MS SQL Server).
Now when I try to use 'sql server management studio' to connect to the database engine, I get this:
=================================== Cannot connect to SW23V01. =================================== Login failed for user 'DOMAIN\Administrator'. (.Net SqlClient Data Provider) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476 ------------------------------ Server Name: SW23V01 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()That's using Windows Authentication. It picks the ID I'm currently logged in as (DOMAIN\Administrator), and it's grayed out, no chance to change the password, so I'm assuming it's just 'passing' my current authentication through.
Also tried SQL Server Authentication, using 'sa' and the password I used when I set the box up. It basically gives me the same error above, same state, severity, error id, etc.
Any thoughts on how to get out of this jam? (My domain isn't DOMAIN, it's obviously something else. Once a paranoid, always a paranoid :) )
sa is still sysadmin, no doubt. However, local admins are no longer SAs in SQL 2008. during the installation, you should face the question: who should be sysadmin on the SQL Server (iirc thetab is called provisioning in the setup). The sa account and everyone you specified here during the setup are the sysadmins. Others are peons. So think hard what you said during the setup and/or what you specified as sa password.
Hope this helps,
-- Erik -- http://blog.rollback.hu
At the very least, I picked the 'use current user' option, and it added DOMAIN\Administrator (which is who I was logged in as).
I'm considering uninstalling, logging in as MACHINE\Administrator, and installing that way, picking 'use current user' when asked. Do you think that might help?
Huh, honestly, no idea. If you specified this account as sa, it *should* work now. Check the install logs at C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log. In a file called details.txt you should find lines containing the following: [timestamp] SQLEngine: --InstallProperties: SqlAdminAccounts = [login names]. Just to make sure... Also, for the sa password, passwords are case sensitive from SQL 2005 - once i found it as a root cause of login failure.
Anyway, this blog post might help you. Nice summary about the logon failure reasons, also it's worth to read the comments, there are additional bits of info.
[UPDATE] I wonder if SQL 2008 still has the feature that if you start it in single user mode, the local admins become sa, no matter what. You might give it a try: on the command line, type sqlservr -m -c. This will give you a single connection.
-- Erik -- http://blog.rollback.hu
Before you reinstall SQL Server, you can try to recover in single user mode. We have detailed instructions in the following article:
I hope this information helps,
- Raul Garcia
SQL Server Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
- Proposed as answer by JorgeBorgos Tuesday, March 01, 2011 2:31 PM
I'll bookmark that for future reference, thank you.
Seems like I won't be needing it now. I uninstalled and re-installed, and not entirely sure what I did differently this time, but it's now letting me use the domain administrator to login through the management studio on the server.
1. I may have been having some issues with my AD server. I finally go fed up with Symantec Endpoint Protection and yanked it (and I had to do some fighting to get my RAS working again after that).
2. In the previous two installs, I 'only' specified domain\administrator as having access to use the management tools and what not. On this last install, I also added domain\domain admins, just in case.
3. Before even 'trying' to use the management studio to connect on this last install, I made sure I installed 2008 SP1 first.
If I were to hazard a guess, I would put my money on 3.
Thanks again for the feedback. Good stuff to read and keep track of.