none
Unable to Connect to SQL Serrver using SQL Server Management Studio

    Question

  • I just installed the 64bit version of SQL Server using the Developer edition CD for SQL Server.  The problem I'm having is that I cannot connect to SQL Server using SQL Server Management Studio.  I’m getting the message “a network-related or instance specific error occurred while establishing a connection to sql server. the server was not found or not accessible. verify that the instance name is correct and that sql server is configured to allow remote connections.”.  I’ve done a lot of Google searches but yet to find a solution.  I've checked to be sure it is started and enabled all protocols except VIA.  I have remote connections allowed.  I used the default name MSSQLSERVER which is what I'm using to connect to in Management Studio.

    Can someone help, I have no idea what to check now.  BTW, I've also installed SP3.

    Thank You,

    Vic


    Visual Basic; Access; PHP; SQL Server; MySQL Developer
    Wednesday, November 03, 2010 11:12 PM

Answers

  • Niel,

    OK got it figured out, it's a UAC issue.  I am part of the Administor's Group and it will not work unless if I right click on SSMS and select "Run As Administrator".  I can also turn UAC off and it works.

    Thanks Neil, I really do appreciate your help in resolving this!

     

    Vic

     

     


    Visual Basic; Access; PHP; SQL Server; MySQL Developer
    • Proposed as answer by Jing Jin - MSFT Thursday, November 04, 2010 3:21 AM
    • Marked as answer by VicSpain Thursday, November 04, 2010 1:20 PM
    Thursday, November 04, 2010 3:07 AM

All replies

  • Are you using the hostname to connect with? You say you are using MSSQLSERVER which means you have installed a default instance but you should be using the hostname. You can try using localhost if you are running SSMS on the same machine as the service. Try using the hostname not MSSQLSERVER and see how you go.

     

    Thanks


    Neil Moorthy Senior SQL Server DBA/Developer
    Wednesday, November 03, 2010 11:50 PM
  • Niel,

    Thank you for the reply.  When I tried localhost I get "cannot connect to localhost". Login failed for <my windows login>.  When I installed it I'm pretty sure I installed it with "Windows Authentication".  How can I verify that?

     If I try to add a SQL Server Login using SSMS, I can't do it because I can't getted logged in.

    Thanks,

    Vic

     


    Visual Basic; Access; PHP; SQL Server; MySQL Developer
    Thursday, November 04, 2010 12:06 AM
  • Ok, are you logged into windows with the same windows login as when you installed the instance? Also in the error message what is the state? You should see something like...

    2006-02-27 00:02:00.34 Logon     Error: 18456, Severity: 14, State: 8.

    2006-02-27 00:02:00.34 Logon     Login failed for user '<user name>'. [CLIENT: <ip address>]

    See the following for more details on narrowing down the login error you are getting...

    http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

    Thanks


    Neil Moorthy Senior SQL Server DBA/Developer
    Thursday, November 04, 2010 12:38 AM
  • Niel,

    Hmm well I'm getting nowhere fast!  I can't view the log file bewcause I can't getted logged in SSMS. So, I navigated to the SQL Server Log Files and tried to view todays file and I'm getting permissions error - contact the owner!  So, I thought I must have screwed up the installation and did not have "Use windows authentication" so I removed everything and went through the install again making sure that "Windows Authentication" was selected.  Well I'm getting the same login error but I can't get to the logfile to get further details.

    Here are the "Details" of the message when the loggin error occurs:

    ===================================

    Cannot connect to localhost.

    ===================================

    Login failed for user '********'. (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

    ------------------------------
    Server Name: localhost
    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()

    Any ideas?

    Thanks Niel,

     

    Vic

     

     

     


    Visual Basic; Access; PHP; SQL Server; MySQL Developer
    Thursday, November 04, 2010 1:54 AM
  • Ok, if your windows login is a member of builtin administrators group you should definately be able to login to SQL Server, sounds like the account you are using to connect via SSMS is not the same one as you installed SQL Server with, otherwise the installer would not have been successful. Did you login as an administrator to do the install? If so do that again, connect and create a login for the windows account you are now trying to use, then re-connect as that. I presume from what you said earlier you did not select mixed mode authentication and specify the 'sa' account password?

     

    Thanks


    Neil Moorthy Senior SQL Server DBA/Developer
    Thursday, November 04, 2010 2:32 AM
  • Niel,

    OK got it figured out, it's a UAC issue.  I am part of the Administor's Group and it will not work unless if I right click on SSMS and select "Run As Administrator".  I can also turn UAC off and it works.

    Thanks Neil, I really do appreciate your help in resolving this!

     

    Vic

     

     


    Visual Basic; Access; PHP; SQL Server; MySQL Developer
    • Proposed as answer by Jing Jin - MSFT Thursday, November 04, 2010 3:21 AM
    • Marked as answer by VicSpain Thursday, November 04, 2010 1:20 PM
    Thursday, November 04, 2010 3:07 AM