August 15, 2008 at 6:05 am
Hello All,
I have just installed SQL Server 2005 (including SP's) onto my Windows 2003 Server and can not connect using the SS Management Studio. I am connecting using Windows Authentication and the user is Admin with all domain rights.
I have used SAC to verify:
- Data Engine is running
- SQL Browse is running
- Remote Connection enable via TCP/IP
- Firewall turned off to make sure that wasn't problem
I am new to SS2005 so am not sure where to get more details about what the problem might be, any help appreciated.
Here are details from server about:
SQL Server Management Studio-9.00.3042.00
Analysis Services Client Tools-2005.090.3042.00
Data Access Components (MDAC)-2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
MSXML-2.6 3.0 6.0
Internet Explorer-7.0.5730.13
.NET Framework-2.0.50727.1433
OS-5.2.3790
August 15, 2008 at 6:09 am
Hi Stephen,
So what do you actually see when you try and connect using SSMS? Are you trying to connect to default or named instance of SQL?
Also, where are you trying to connect from? i.e SSMS on the server, your workstation, remotely (across vpn etc)
August 15, 2008 at 6:10 am
What is the error message you are getting when you try to connect? Are you running SSMS on the SQL Server or remotely?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 15, 2008 at 6:16 am
Thanks for the very quick responses. I am connecting on the Win2003 server, same machine SS2005 is on. Yes I am connecting to a named instance since I already have SS2000 on the server. Below is the text I copied from the error. I had followed the help link and tried all suggestions.
===================================
Cannot connect to ANTEC2SERVER\SQL2005.
===================================
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
------------------------------
Error Number: -1
Severity: 20
State: 0
------------------------------
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.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
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()
August 15, 2008 at 6:51 am
If you are running SSMS on the console then you should not need remote connections enabled. Try localhost\instance.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 15, 2008 at 6:59 am
Try logging in using SQLCMD, so from a command prompt window type the following command
sqlcmd -S ANTEC2SERVER\SQL2005 -E
-S server
-E trusted connection
quit to exit
August 15, 2008 at 7:02 am
Hello Jack,
>> if you are running SSMS on the console then
>> you should not need remote connections enabled.
Should I disable them? I will be accessing SSMS from my other PC in the future so figured I would need it enabled
>> Try localhost\instance.
Under the Connect to Server dialog - login I have Server Name: myservername\myinstancename, wasn't sure if you meant for me to actually use 'LocalHost' but I did try it and get the same error
thanks
August 15, 2008 at 7:11 am
I agree with Jack you should not need to enable 'remote connections' even if you intend to connect using SSMS from your own desktop. Although this may depend upon how your network's configured.
In the SAC tool what protocols are enabled?
August 15, 2008 at 7:20 am
I did not mean for you to disable remote connections, I was just commenting that they do not need to be enabled when you are on the console. Since you are able to connect using SAC you should be able to connect using SSMS. Are you sure you have the everything spelled correctly? If you installed using Mixed authentication can you connect using sa?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 15, 2008 at 7:33 am
Hello Mark,
Ok will disable remote connection for now.
re: SAC and protocols - sorry for ignorance but I couldn't see where any protocol were listed except on the remote connections and I did have only TCP/IP. I looked under 'Services and Connections' and 'Configuring Features'
I am probably missing something simple becuase this is my 1st run at 2005.
I tried using sqlcmd and got the following error:
C:\>sqlcmd -s antec2server\sql2005 -e
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
I also read on an FAQ somewhere else about trying...
telnet {servername} 1433
to see if that even worked (don't know what it does) however that did fail. It mentioned about looking in errorlog to see what ports sql server was listening and I found these 4 line together... but when I tried the other ports they failed also.
Server is listening on [ 'any' 3897].
Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLSERVER2005 ].
Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLSERVER2005\sql\query ].
Server is listening on [ 127.0.0.1 2858].
FWIW, there are no entries in the errorlog about login errors, actually I couldn't see any error at all. Plus the log was hours old
Thanks again all
August 15, 2008 at 7:38 am
Argh GOT IT!
It was Jack's comment about making sure the name was correct. When logging in I wasn't typing anything, just selecting what was listed in the "Server Name:" combo box.
ANTEC2SERVER\SQL2005
Then I looked closer at what SAC was connected to and it had listed
SQLSERVER2005
So changed above to that an I'm in.
So sorry to have wasted y'alls time. FWIW, I Have no idea where SQL2005 came from
Thanks again
August 15, 2008 at 7:48 am
We've all been there. Just yesterday I was fighting with using a variable in SSIS for an Excel connection and it was because I had mispelled the file name in the default for the variable. I spent like 30 minutes until I notice the misspelling.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 15, 2008 at 8:00 am
We sure have Jack... lol. At least everything's resolved now Steven.
By running telnet sqlservername 1433 from a cmd prompt this will establish whether you are able to connect to SQL (SQL default port is 1433). Your named instance will also be listening on a seperate port, you can find this out via sac or the sql log files. A quick google search will enlighten you!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply