Cannot get SQL Server Management Studio Express to connect to SQL Server 2005

</o>
I?m having trouble getting SQL Server Management Studio Express to connect to my SQL Server 2005 database at DiscountASP.
</o>
I checked the knowledge base, searched the database forum, manually scanned the forum back to 2004 and have Goggle?d, but to no avail.
</o>
I followed all the steps in the FAQ article Q10369 - How to connect to SQL Server 2005 using SQL Server Management Studio?
</o>
Then I did a web search for the error message I was getting, and came to the conclusion it may be because SQL Server Express was not set up for remote access. So I followed the steps listed at: http://www.datamasker.com/SSE2005_NetworkCfg.htm
</o>
Then I rebooted. Still wouldn?t connect. I tried changing the <st1lace w:st="on"><st1laceName w:st="on">TCP/IP <st1laceType w:st="on">Port from 1433 to 14330 (KB article Q10385). No change. Then I went to Windows Firewall. I couldn?t find a list of ports, but I did add ports 1433 and 14330, but still no connection.
</o>
Then I tried going further than Q10369 and clicked on the Options button in SQL Server Management Studio Express and set ?Connect to database? to the name of my SQL 2005 database. Still wouldn?t work.
</o>
Then I set ?Network Protocol? from Default to TCP/IP and set connection Timeout to 20 seconds. Still no go.
</o>
The connection strings in my ASP application (which work fine) are all prefaced with ?TCP:?. So I tried
Connecting using the ?TCP:? preface and also without it.
</o>
Then I tried every combination of the above. Depending upon whether I used the ?TCP:? preface or not, the error message would change. I went to the URLs in the error messages and they said Microsoft had no information on this error number.
</o>
Would anyone have any idea as to what I?m doing wrong? Here are the two types of error messages (Note I?ve put asterisks in the User Name):
</o>
</o>
===========================================================
Using tcp:sql2k502.discountasp.net
===========================================================
</o>
TITLE: Connect to Server
------------------------------
</o>
Cannot connect to tcp:sql2k502.discountasp.net.
</o>
------------------------------
ADDITIONAL INFORMATION:
</o>
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: TCP Provider, error: 0 - No such host is known.) (Microsoft SQL Server, Error: 11001)
</o>
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&amp;EvtSrc=MSSQLServer&amp;EvtID=11001&amp;LinkId=20476
</o>
------------------------------
BUTTONS:
</o>
OK
------------------------------
</o>
===========================================================
Using sql2k502.discountasp.net
===========================================================
</o>
TITLE: Connect to Server
------------------------------
</o>
Cannot connect to sql2k502.discountasp.net.
</o>
------------------------------
ADDITIONAL INFORMATION:
</o>
Login failed for user 'SQL2005_******_*******_user'. (Microsoft SQL Server, Error: 18456)
</o>
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&amp;EvtSrc=MSSQLServer&amp;EvtID=18456&amp;LinkId=20476
</o>
------------------------------
BUTTONS:
</o>
OK
------------------------------
</o>

 

The errors you are receiving is either a network connectivity issue and/or you are not inputting the correct login or password.

First try performing a traceroute test to the SQL server and make sure that it is resolving to the correct IP address and you do not have any major disruptions from your local machine to our server. Make sure you are getting good responses back from the many hops you encounter.

Then try doing a telnet test to the sql server. Do two telnet test, one to port 1433 and the other is to port 14330.

telnet sql2k502.discountasp.net 1433

and

telnet sql2k502.discountasp.net 14330

For the db login try resetting it if you have not done so. Also perform this test in MS DOS and see if you can log in.

osql -S [SQL Server] -U [DB Login] -P [Password]

This test is usefull cause it makes the servername, db login, and password visible so you know for sure if you are inputting the correct login and password.

 

Raymond;
</o>
Thanks for your help and suggestions.
</o>
I double checked The UserID and Password, both are correct.
</o>
I did the telnet test to 1433 and 14330. It said ?Connecting to sql2k502.discountasp.net 1433?.? (the second test it said 14330)
</o>
And a minute or two later it came back and said ?Press any key to continue?, then it said ?Host connection Lost? after I hit return.
</o>
I did the Traceroute test at http://www.dnsstuff.com/. The results arebelow.
</o>
I have to admit I?m not sure how to interpret either the Telnet nor the Traceroute results.
</o>
Then I pinged from the same site. The results were:
</o>
Pinging sql2k502.discountasp.net [64.79.160.16]:</o></o><st1lace w:st="on">Ping #1: Got reply from 64.79.160.16 in 35ms [TTL=55]</o><st1lace w:st="on">Ping #2: Got reply from 64.79.160.16 in 35ms [TTL=55]</o><st1lace w:st="on">Ping #3: Got reply from 64.79.160.16 in 34ms [TTL=55]</o><st1lace w:st="on">Ping #4: Got reply from 64.79.160.16 in 34ms [TTL=55]</o></o>Variation: 0.4ms (+/- 1%)</o>Shortest Time: 34ms</o>Average: 34ms</o>Longest Time: 35ms</o>
</o>
Next I did the Osql test you recommended. I was able to connect, query my database, and Osql returned the data rows I requested.</o>
</o>
So I tried resetting the logon for SQL Server Management Studio Express and entering in all the information anew. However, it still returns the following error when I try to log on:</o>
</o>
TITLE: Connect to Server
</o>
Cannot connect to tcp:sql2k502.discountasp.net.
</o>
<I style="mso-bidi-font-style: normal">Login failed for user 'SQL2005_360465_dpg2007_user'. (Microsoft SQL Server, Error: 18456)</o>[/I]
</o>
What strikes me odd is that I can access the database using Osql.exe but I cannot using SQL Server Management Studio Express. Further, using or not using the prefix ?TCP:? in the server name doesn?t make a difference in Osql but it will generate two different error messages in SQL Server Management Studio Express.</o>
</o>
Does any of this help?</o>
</o>
Thanks,</o>
</o>
Lar


</o>
</o>
</o>

Traceroute to sql2k502.discountasp.net</o>
Generated by www.DNSstuff.com at 17:10:11 GMT on 28 Jun 2007.</o>





Hop</o>

T1</o>

T2</o>

T3</o>

Best</o>

Graph</o>

IP</o>

Hostname</o>

Dist</o>

TTL</o>

Ctry</o>

Time</o>


1</o>

0</o>

*</o>

*</o>

0.6 ms </o>






</o>

</o>
</o>

74.53.59.130 AS0
IANA-RSVD-0</o>

82.3b.354a.static.theplanet.com.</o>

</o>

255</o>

US</o>

Unix: 17:10:12.265</o>


2</o>

0</o>

*</o>

*</o>

0.6 ms [+0ms]</o>






</o>

</o>
</o>

12.96.160.9 AS21844
THEPLANET-AS</o>

vl1.dsr01.dllstx2.theplanet.com.</o>

0 miles [+0] </o>

254</o>

US</o>

Unix: 17:10:12.276</o>


3</o>

0</o>

*</o>

*</o>

0.8 ms [+0ms]</o>






</o>

</o>
</o>

70.87.253.113 AS21844
THEPLANET-AS</o>

71.fd.5746.static.theplanet.com.</o>

0 miles [+0] </o>

253</o>

US</o>

Unix: 17:10:12.338</o>


4</o>

0</o>

*</o>

*</o>

0.8 ms [+0ms]</o>






</o>

</o>
</o>

70.87.253.1 AS21844
THEPLANET-AS</o>

et3-1.ibr03.dllstx3.theplanet.com.</o>

0 miles [+0] </o>

61</o>

US</o>

[Router did not respond]</o>


5</o>

2</o>

*</o>

*</o>

2.1 ms [+1ms]</o>






</o>

</o>
</o>

208.172.139.129 AS3561
SAVVIS</o>

dcr2-ge-4-0-0.dallas.savvis.net.</o>

0 miles [+0] </o>

250</o>

US</o>

Unix: 17:10:12.390</o>


6</o>

46</o>

*</o>

*</o>

34 ms [+32ms]</o>






</o>

</o>
</o>

204.70.192.245 AS3561
SAVVIS</o>

dcr1-so-7-2-0.losangeles.savvis.net.</o>

0 miles [+0] </o>

249</o>

US</o>

Unix: 17:10:12.489</o>


7</o>

44</o>

*</o>

*</o>

34 ms [+0ms]</o>






</o>

</o>
</o>

208.172.35.62 AS3561
SAVVIS</o>

ahr1-pos-0-0.irvine2oc2.savvis.net.</o>

0 miles [+0] </o>

249</o>

US</o>

Unix: 17:10:12.554</o>


8</o>

34</o>

*</o>

*</o>

34 ms [+0ms]</o>
<TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 0in; BORDER-TOP: #ebe9ed; PADDING-LEFT:

 

The results you have provide sure sends mixed results. I have yet to see the error message 'Login failed for user 'SQL2005_360465_dpg2007_user'. (Microsoft SQL Server, Error: 18456)' being attributed to network connectivity issues. Yet the telnet test indicates that port 1433 and 14330 is beig blocked or disrupted. But the osql.exe test by default should be using port 1433 and you are able to access it without any problem. The OSQL test does prove that our db server is fully functional.
Can you give us screen shots to the connection and login settings you have for your SQL Server Management Studio Express.
Also not only you can explicitly specify what protocol you can use but the port also . tcp:sql2k502.discountasp.net,1433

 

Raymond;

Thanks again for the assistance.

Per your suggestion, I tried usingtcp:sql2k502.discountasp.net, 1433to login also, but that didn't not work.

Attached are two of four total screen shots.

Again, I really appreciate the help.

Lar

 

Here's the other two screenshots.

 

As you noted, OSQL will connect fine but SSMSE will not. Today I tried connecting using Database Explorer in Visual Web Developer Express, andI was able to connect directly to my SQL 2005 database without a problem. Unfortunately, VWD Database Explorer is a Read-Only connection, so I can't change data, add columns, etc.


But it certainly seems odd that VWD and OSQL will connect but SSMSE will not.

 

Raymond, as I was thinking about yourobservation that for some reasonports1433 and 1430 were being blocked for SSMSE, I shut down the firewall completely, hoping that might let the connection complete, and then tried connecting again via SSMSE. Alas, still no brass ring.

 

Frustrated, I took a long hiatus from this problem and just started reworking it again today.
</o>
I finally got SSMSE to work with SQL 2005? but have to admit that I?m unsure which of the steps I took it was that actually fixed things.
</o>
However, I spent 8+ straight hours today and probably 40+ in previous months, and I found some curious things not listed in previous posts on this topic (or if they were, I missed them).
</o>
So I?m going to list all I?ve done today in the hope it will save someone else all the grief and raised blood pressure this one has put me through.
</o>
First let me say that while the folks at DASP did not provide the final fix, I could not have arrived here without their very valuable assistance. This turned out to be a long process of elimination, and I would have never discovered what was causing the problem without their help in eliminating what wasn?t causing the problem. Thank you.
</o>
I tried connecting dozens of times, in just about every configuration imaginable, as well as reentering all the particulars (database name, user ID, password, etc) numerous times, and still no go.
</o>
It was especially frustrating because I could use the Database Explorer tool in Visual Web Developer Express to connect to SQL 2005 without a problem. So it certainly wasn?t a network connectivity issue.
</o>
But it couldn?t be the User ID and Password combination either, for I tried reentering them a dozen times just today alone. I also tried copying and pasting them from the DiscountASP SQL 2005 connection string in web.config (which works fine) and still got the same message:
</o>
Login failed for user 'SQL2005_*****'. (Microsoft SQL Server, Error: 18456)
</o>
Then I found the following Microsoft article, explaining why you might not be able to connect if you installed SQL Express as Windows Authentication, and later tried to use SQL Server Authentication, and why such would generate Error 18456:
</o>
http://msdn2.microsoft.com/en-us/library/ms188670.aspx
</o>
Which was exactly what I had done. I found that SQL Express was set up for Windows Authentication only, so I followed the steps in the above article, rebooted, then tried logging on in a variety of configurations, using ports 1430, 1433, and 14330, and all to no avail.
</o>
Let me note that at this point I was doing a copy/paste on all login information to speed things up.
</o>
When doing a Google search on the error message, I found another Microsoft article which describes how to determine specifically what is causing Error 18456:
</o>
http://msdn2.microsoft.com/en-us/library/ms366351.aspx
</o>
However, you must have access to the SQL Server 2005 Error Log to find what?s the source of the problem, and you would need to get help from DASP on that one. But before contacting them I wanted to see what else I could find.
</o>
So I turned back to Google. I finally found a post that said you could not copy/paste the login information for SSMSE because there was a bug:
</o>
http://forums.yetihost.com/showthread.php?t=677
</o>
I tried manually entering all the information (which I had done numerous times before, but not before changing the authentication for User ?sa? as described in the first link above) and ? viola! ? I was connected.
</o>
Taking a SWAG at it, I?d say installing as SQL Server Express as Windows Authentication (first link above) was one of the base problems, and then once that was fixed it was the cut-and-paste that was doing me in.
</o>
But if you?ve read this entire thread, you will have seen that I tried a ton of things over the past few months, so maybe my SWAG is wrong.
</o>
At any rate, I hope this post helps someone else as much as a lot of the other posts have helped me.
</o>
<I style="mso-bidi-font-style: normal">Laissez les bons temps rouler![/I] (Let the good times roll)


Lar

 

Thanks for the post. I am not sure whether setting the Authentication Mode to Mixed mode or manually typing the server name / login / password that fixed my problem but it works now.

 

You can copy and paste everything except your password. That was my experience anyways.
This forum should have "Stickies", cause this one definitely deserves it. Thanks goes out to the author of this thread. Yo Rock..

JBanks

And last but not least..... food for the Google spiders..


discountasp.net + Microsoft SQL Server Error 18456 + Server Management Studio Express + Copy and pasting SQL password causes Error 18456 + Resolution: Manually type your password in.
OS: Windows Vista Home Premium edtion

Microsoft SQL Server Management Studio Express9.00.3042.00
Microsoft Data Access Components (MDAC)6.0.6001.18000 (longhorn_rtm.080118-1840)
Microsoft MSXML3.0 4.0 5.0 6.0
Microsoft Internet Explorer7.0.6001.18000
Microsoft .NET Framework2.0.50727.1434
Operating System6.0.6001