Update 2: Could not open a connection to SQL Server instance

Contributors

*Change style?

Blog Administration

< Update: Could not open a connection to SQL Server instance | Coldfusion - *Free* for academic use >

Saturday, September 27. 2008

Update 2: Could not open a connection to SQL Server instance

Further verification revealed that I still could not connect to the instance. After reporting the issue to the developers and Network gurus, I decided to poke around. I started with the sqlcmd utility. Siimply typing

sqlcmd -S tcp:bpa-sqlserver.murraystate.edu\sqlexpress,1433
(from the command prompt - start/run cmd)

gave me the following error, "Login failed for user ' ' The user is not associated with a trusted server connection". After checking the syntax for the sqlcmd utility, I added the userid and password options (- U and -P - sometimes command line utilities are SO handy!). So I attempted again with these two options

sqlcmd -U myUserId -P mypassword -S tcp:bpa-sqlserver.murraystate.edu\sqlexpress,1433

and I got a "1>" prompt immediately. So now I knew I could connect and the error had to be with the Sql Server Management Studio (SSMS).

My fresh attempt gave me another error - the famous "error 26: Error locating server/instance specified". The only difference between the command line version and the studio version: server name. I added the port to the Server name and it connected instantly!

bpa-sqlserver.murraystate.edu\sqlexpress,1433

I suspect what I am saying next has to do with the server installation because I was able to connect even after shutting down every SQL server service running on my local machine. However, for those trying to connect to your local machine ("server") from another computer, this will be relevant and useful. One other point to note: Check to see if the tcp/ip is enabled (on the server). You can do this from the configuration manager

Start / Run and type in SQLServerManager.msc

. Just rt-click and enable the protocol. It will respond with "Any changes made will be saved; however, they will not take effect until the service is stopped and restarted."

Note: This does not seem to affect the client.

Posted by Victor Raj in SQL server at 12:00 | Comments (3)

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

Am I correct in seeing that what was done to fix is to specify the port # in the connection string?

The connections get funneled in to that port and then locally go to their designated instance?

#1 Andy Hill on 2008-09-29 14:52

Actually, all I am able to do is connect to the default instance (regardless of the instance named). Found that out because I used two different passwords for the two instances. However, the connection from within the campus works fine with jsut the named instance.

#1.1 Victor Raj on 2009-03-31 16:33

Yes, i agree. I think it may be a server installation problem.

#2 Brad Benford (Homepage) on 2011-03-06 15:34

The author does not allow comments to this entry