| Author |
Topic |
|
andym0908
Starting Member
11 Posts |
 Posted - 2008-04-28 : 07:06:23
|
| Hi all.. my first post here, so be gentle with me!I've got a dedicated server which will host my site, and I've installed SQL Server 2005 Developer. My aim (as has been for a long time!), is to connect my local Management Studio to the server copy of SS2005.So far I've tried everything possible to connect but I'm always getting the "Login Failed" error. Using a login I created, the state code was 5, and using 'sa' the state code was 8. Now I've quadruple checked spellings/case and everything is in order.I've also checked firewalls, security policies, the sql login properties, unticking "enforce password policy" etc...I've also just noticed that the log is filling with constant connection events for 'sa' (which are failing).I am really stuck here.. Does anyone have a clue or a checklist of things to run through??Many thanks in advanceAndy |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2008-04-28 : 07:17:40
|
| 1. is the password for sa correct?2. how do you connect to the server? do you have an alias in your client network utility (configuration manager)?3. did you change the default port number that sql listens to?4. is your sql browser running?--------------------keeping it simple... |
 |
|
|
andym0908
Starting Member
11 Posts |
Posted - 2008-04-28 : 07:23:34
|
| Hi1. Yes, the SA password is correct.2. I'm just using the IP in Management Studio when connecting to a new database engine.3. I've not changed any ports that I know of.4. Yes SQL Browser is running. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2008-04-28 : 07:37:41
|
| Is your sql server installed as default or a named instance?#3. you can check if the port is changed from dynamic to static by going to the sql server surface configuration manager\network configuration and check the properties for the protocols enabled... tcp protocol...under ip all tcp port, check if it's dynamic or something different is set--------------------keeping it simple... |
|
|
|
andym0908
Starting Member
11 Posts |
Posted - 2008-04-28 : 07:56:43
|
| SQL Server was installed as a default instance..In config manager, all protocols apart from VIA are enabled. The TCP/IP settings are not dynamic (IP address is set, and TCP Dynamic Ports has no field value) - the port is 1433. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2008-04-28 : 07:59:44
|
quote:
Originally posted by andym0908
SQL Server was installed as a default instance..In config manager, all protocols apart from VIA are enabled. The TCP/IP settings are not dynamic (IP address is set, and TCP Dynamic Ports has no field value) - the port is 1433.
then try the ipaddress,1433 in your server connection...--------------------keeping it simple... |
|
|
|
andym0908
Starting Member
11 Posts |
Posted - 2008-04-28 : 08:17:50
|
| Same result.I tried xxx.xxx.xxx.xxx:1433 which took longer, but failed.Trying xxx.xxx.xxx.xxx,1433 errored instantly. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2008-04-28 : 08:28:34
|
| on the server do a select @@servername, use osql to do this...--------------------keeping it simple... |
|
|
|
andym0908
Starting Member
11 Posts |
Posted - 2008-04-28 : 09:11:40
|
| Ok, did that and got: DSVR006493\MRPDB |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2008-04-28 : 09:26:29
|
quote:
Originally posted by andym0908
Ok, did that and got: DSVR006493\MRPDB
then use... DSVR006493\MRPDB,1433--------------------keeping it simple... |
|
|
|
andym0908
Starting Member
11 Posts |
Posted - 2008-04-28 : 11:46:49
|
| No good. "An error has occurred while establishing a connection to the server"blah blah"provider: TCP Provider, error 0:No such host is known" |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2008-04-28 : 14:12:43
|
ok, since i asked you 4 questions and 2 out of 4 of your answers were not correct as we've seen so far, try to verify them again...otherwise, create an alias under configuration manager on your local machine and see if that works for youalso do a netstat to the sql server if you are using the correct port number, and telnet it as wellquote:
Originally posted by andym0908
No good. "An error has occurred while establishing a connection to the server"blah blah"provider: TCP Provider, error 0:No such host is known"
--------------------keeping it simple... |
|
|
|
andym0908
Starting Member
11 Posts |
Posted - 2008-04-28 : 16:48:35
|
| To recap...The passwords I am using are 100% correct.SQL Browser service is running on remote DB.I created an alias to the specific IP and port - no luck.I'm not familiar with netstats - I don't know what to look for.Same with Telnet - it attempts to connect using port 23, but fails.I'm going to try SQL Profiler to see what, if anything, is being picked up remotely. |
|
|
|
andym0908
Starting Member
11 Posts |
Posted - 2008-04-28 : 16:50:27
|
| Also.. if I connect (via Remote Desktop) to the server hosting the database - I can connect using SQL Authentication using any of the users (including SA)... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-28 : 16:55:08
|
| Test with telnet like this: telnet ServerName PortNumberThe test must run on the client machine in order for it be a valid test.Do you get an error or a blank screen?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
andym0908
Starting Member
11 Posts |
Posted - 2008-04-28 : 17:30:58
|
| Ok with the Telnet test, I get a blank screen.I used the site: http://www.canyouseeme.org/ And the IP+Port is not being blocked.. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-28 : 21:59:30
|
| Do you have completed login error? |
|
|
|
andym0908
Starting Member
11 Posts |
Posted - 2008-04-29 : 07:34:25
|
| No - I'm still stuck.SQL Profiler didn't show anything coming up at all.. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-29 : 22:34:49
|
| But you said you got login failed error? What was whole error message? |
|
|
|
andym0908
Starting Member
11 Posts |
Posted - 2008-05-01 : 05:46:18
|
| Same error as I already wrote befor... from the Log:Error: 18456, Severity: 14, State: 8.2008-05-01 10:40:51.60 LogonLogin failed for user 'sa'. [CLIENT: xxx.xxx.xxx.xxx]Despite the State '8' meaning "Password Mismatch" I can assure that the password is 100% correct. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-01 : 23:45:03
|
| Then you need find out sa pwd. |
|
|
|
Next Page
|
Remote Connection Issues