remote connectivity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

remote connectivity

Author

Topic

AndrewB
Starting Member

11 Posts

Posted - 2007-10-10 : 15:46:50

i am trying to connect to sql server remotely with native client. tcp/ip and named pipes are enabled on both sides. there is no firewall between client and server. surface config on server allows remote connections. i have been working on this for a week. i am new to sql server 2005 dont know where to go from here. thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-10 : 15:48:28

Providing the error would be helpful.

Have you created an alias on the client machine?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

AndrewB
Starting Member

11 Posts

Posted - 2007-10-10 : 15:55:32

the error is below. i get this when i type just the name of the server in but not the instance. i installed with a default instance, do i need to specify that at logon? i think the default is mssqlserver, does that sound right? im not sure about creating an alias. thanks

TITLE: Connect to Server
------------------------------

Cannot connect to WINSERV1.

------------------------------
ADDITIONAL INFORMATION:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-10 : 16:12:54

If you installed the default instance, then you do not provide an instance name when connecting.

Creating aliases is done in the SQL Server Configuration Manager tool. You will be doing this on the machine where you are receiving the error and not on the actual database server. In the tool, go to Alias under SQL Native Client Configuration. Right click on Aliases and select new alias. Use the hostname of the database server for the alias, specify the port that the sql server is listening on in port field (by default, sql server will use 1433), keep TCP/IP as the protocol (should already be selected since it's the default), specify the hostname of the database server again in the server name field.

Try connecting again.

If that doesn't work:

Navigate to this key in regedit:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

Post exactly what you have for the alias you just selected.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

AndrewB
Starting Member

11 Posts

Posted - 2007-10-10 : 17:18:57

i am getting a different error now:

TITLE: Connect to Server
------------------------------

Cannot connect to WINSERV1.

------------------------------
ADDITIONAL INFORMATION:

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)

here is the alias:

WINSERV1 REG_SZ DBMSSOCN, WINSERV1, 1433

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-10 : 17:24:13

Are you able to ping WINSERV1 from the client machine that is receiving the error? Are you sure that SQL Server on WINSERV1 is listening on port 1433? What operating system is WINSERV1?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

AndrewB
Starting Member

11 Posts

Posted - 2007-10-10 : 17:43:24

Tara, Thank you so much for your help today. I have to quit troubleshooting for today, but I'll be back on tomorrow. Thanks again and hopefully I'll talk to you tomorrow.

Andrew

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-10 : 22:54:14

Is it named instance? If so, should call it like host_name\instance_name. Double check instance name in sql server configuration manager. Or create alias with server's ip address and sql port then try connect again.

AndrewB
Starting Member

11 Posts

Posted - 2007-10-11 : 14:26:47

i have a default instance, i uninstalled my named to make things easier. i can ping the machine where sql server is installed. the operating system thats being used is windows server 2003. the server is listening on port 1433 as per the errorlog. "server is listening on 'any' 1433". here is the error message i get now when trying to connect:

TITLE: Connect to Server
------------------------------

Cannot connect to WINSERV1.

------------------------------
ADDITIONAL INFORMATION:

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)

i am using windows auth, is that ok? also, during setup i didnt setup a username and password and domain, i just used the local. is that ok? thanks for all the help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 14:54:07

Yes windows authentication is ok, in fact it's preferred for security reasons.

The username/pwd/domain thing is for the service and that shouldn't matter here.

Are you on the same domain as the database server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

AndrewB
Starting Member

11 Posts

Posted - 2007-10-11 : 15:18:29

the client is on the same network as the server,and as far as i know its on the same domain as well

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 15:28:23

From the client machine, attempt to telnet to port 1433 to the WINSERV1 machine. If that doesn't work, then there is some network/firewall problem.

To do this, open a cmd window (Start..Run) on the client machine, type in telnet WINSERV1 1433. Hit enter. What do you see?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

AndrewB
Starting Member

11 Posts

Posted - 2007-10-11 : 16:02:38

doesnt look good. here is what i got.

C:\>telnet WINSERV1 1433
Connecting To WINSERV1...Could not open connection to the host, on port 1433: Connect failed

again, i have no firewall on the server. there is one on the client but its stateful so it shouldnt block anything going out. i allowed tcp 1433 and udp 1434 anyway. i can ping the ip address of the server but cant ping winserv1. it says 'could not find host winserv1'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 16:11:18

If you can't ping WINSERV1 by name, then you must use the IP address. Go to the alias that you created and change the server name field to the IP address. Keep the alias name as WINSERV1.

You've got a DNS issue, so you'll need to use the IP rather than the hostname.

Next time you say you can ping WINSERV1, let us know that it is only by IP address as that would have saved us a lot of time.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

AndrewB
Starting Member

11 Posts

Posted - 2007-10-11 : 16:23:25

I apologize about the host and ip thing. here is my new error:

TITLE: Connect to Server
------------------------------

Cannot connect to 192.168.1.10.

------------------------------
ADDITIONAL INFORMATION:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

here is the alias from regedit:

WINSERV1 REG_SZ DBMSSOCN, 192.168.1.10, 1433

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 16:25:24

This is much better. You are actually getting connected now, but are having access issues.

Are you specifying Windows Authentication in SSMS? If so, are you logged into the client machine with a domain account?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

AndrewB
Starting Member

11 Posts

Posted - 2007-10-11 : 16:31:05

windows auth is specified. the client im trying to connect with is a laptop thats connected to the network. when i boot up, i log on with the administrator account. i have to stop today, i have to get to class. again, thanks SO much for your help. i have a great deal from troubleshooting with you. talk to you in the morning.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 16:35:10

Using the administrator account will not work. If you can't login with a domain account, then you'll need to specify SQL authentication and provide a valid SQL account and password to get in.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

AndrewB
Starting Member

11 Posts

Posted - 2007-10-12 : 11:53:22

good morning. do i need to logon as sa from the server to create a username and password? i didnt specify one at install, do i need to reinstall?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-12 : 11:57:20

So are you saying that you didn't install it with Mixed Mode authentication and can't use SQL authentication? If so, go to the actual database server, login with Windows authentication with an account that has local admin, then change the authentication modes in SSMS, add your account, go back to your laptop, try SQL authentication with this new account.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

AndrewB
Starting Member

11 Posts

Posted - 2007-10-12 : 12:19:29

im in with sql auth. thanks so much for your help!!!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources