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.
| Author |
Topic |
|
dolphin123
Yak Posting Veteran
84 Posts |
 Posted - 2014-09-16 : 11:12:26
|
| SQL server 2008 R2. It has default instance running and a couple of named instance.- I can connect to both default instance and named instances.- User can connect to default instance but he is not able to connect to named instances.error is "26 - Error Locating Server/Instance required" screencast.com/t/Kvbic3WlNq- I asked the user to connect "tcp:ServerIP\instancename" and it worksI thought then it could be that UDP port 1434 is not allowed (which is important for names instance connection), but I am not able to connect the usual way (servername\instancename)My system administrator tells me all the ports are allowed to this hosting network on the SQL boxes including the UDP ports. Also, SQL browser is running - screencast.com/t/eh0qIvSntY0Q (here is a screenshot. I blocked the domain name)I have 3 questions:1. What could be blocking the user/s in a particular network not be able to connect to it?2. Is it bad if I allow the user to connect to is using "tcp:ServerIP\instancename" ? Why don't usually people don't use this? 3. What does it mean when a user can connect to an instance using "tcp:ServerIP\instancename" but not "ServerIP\instancename" |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-09-16 : 12:11:16
|
It sounds like the user has been configured to use a protocol different than TCP/IP as the default and that the server instance does not support the user's default protocol. You could use SQL Server Configuration Manager to change the user's default protocol. If the user is trying to connect via SSMS, the error message will say which protocol is being attempted (i.e. Can't connect using Names Pipes). Using the "TCP:" prefix in the connection string just overrides the user's default setting. It's not wrong, strictly speaking, to specify the protocol but if you want user to connect using TCP/IP then I would make the configuration change at the user level.
Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy |
 |
|
|
dolphin123
Yak Posting Veteran
84 Posts |
Posted - 2014-09-16 : 13:22:13
|
| User has TCP/IP enabled just next to Shared memory. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-09-16 : 13:51:33
|
| Just add a SQL alias to the client machine:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectToNew String ValueName - whatever you want to refer to it as, it is an aliasValue data - DBMSSOCN,ServerNameOrIpAddress,PortNumberIf it's a 64-bit machine, also add it here:HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectToTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
dolphin123
Yak Posting Veteran
84 Posts |
Posted - 2014-09-16 : 16:13:11
|
| Thanks Tara.Say I use like this:DBMSSOCN,<servername>,14331433 is the port default instance is running on. After adding this entry, should the user be connecting his SSMS to <ALIASNAME>\<NAMEDINSTANCE>? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-09-16 : 16:23:26
|
| It depends what the registry entry is named. Whatever you name it is what needs to be used for the server name in SSMS. So if you used <aliasname> for the registry entry, then the user would use <aliasname> for the server name. You can name that alias whatever you want, what matters is the value data inside the registry entry. The value data is what redirects it.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
SQL named instance cannot be connected