topic Re: Can't Connect to new SQL Server Instance with SQL Manager in ArcGIS Enterprise Questions

Can't Connect to new SQL Server Instance with SQL Manager

JoeBorgione — Wed, 13 Apr 2016 20:59:49 GMT

This really isn't a GIS question per se, but I'm hoping someone can get me moving in the right direction.

Today IT created a new instance of SQL Server on which I have created a new Enterprise Geodatabase. If I log into the actual machine where SQL Server resides, I can use SQL Server2014 Management Studio just fine to administer the new database. While in the same session of Management Studio, I can connect to another instance of SQl Server on a different server machine.

My problem is I can't connect to the new SQL Server Instance from my desktop machine which is on the same network/domain using SQL Server2014 Management Studio. It tosses a fairly generic error about pipes etc; sometimes it's error 2, sometimes 54 I checked the the new instance properties and 'Allow Remote Connections To This Service' is checked. I guess remotely logging into the server isn't the end of the world, but I'd rather just hit from my desk top.

Vince Angelo...

Re: Can't Connect to new SQL Server Instance with SQL Manager

JoshuaBixby — Thu, 14 Apr 2016 00:39:40 GMT

It helps to give specific error messages/text instead of just saying you got a "fairly generic error about pipes."

There could be many things going on, have you tried anything else besides just connecting with SSMS? Can you ping the server IP address? Do the servers run firewalls that may be blocking the connections? My guess, though, the Network Protocol isn't configured for correctly. Check the network protocol configurations on both the client and server.

Re: Can't Connect to new SQL Server Instance with SQL Manager

JoeBorgione — Thu, 14 Apr 2016 11:44:16 GMT

Thanks- when I get back on site I'll capture the specific error popups.

Re: Can't Connect to new SQL Server Instance with SQL Manager

SamuelTompsett — Thu, 14 Apr 2016 16:21:54 GMT

Hi Joe,

Make sure that Named Pipes are enabled in SQL Server Configuration Manager on the Server. Without Named Pipes enabled, you wont be able to connect from ArcGIS on a machine other than on the Server.

Hopefully that helps.

-Sam

Re: Can't Connect to new SQL Server Instance with SQL Manager

JoeBorgione — Thu, 14 Apr 2016 16:50:11 GMT

I can connect using ArcGIS/direct connect from another machine no problem...

Re: Can't Connect to new SQL Server Instance with SQL Manager

SamuelTompsett — Thu, 14 Apr 2016 17:14:40 GMT

On your desktop, do you have port 1433 open?

Re: Can't Connect to new SQL Server Instance with SQL Manager

JoeBorgione — Fri, 15 Apr 2016 18:35:29 GMT

I'm thinking so because I can use SSMS to administer a different instance on a different machine from my desktop. When I'm back on site Monday I'll do some poking around on the new server.

Re: Can't Connect to new SQL Server Instance with SQL Manager

JoeBorgione — Mon, 18 Apr 2016 19:53:21 GMT

Thanks Samuel Tompsett. Once I enabing named pipes, I'm able to use the instance name and connect from my desktop. It.s odd though, if I use the internal ip address I get this:

Joshua Bixby, this is what I meant by generic; the other day I would get this and another error, both word for word, but the last error was error:67 as opposed to error:2

Re: Can't Connect to new SQL Server Instance with SQL Manager

JoshuaBixby — Tue, 19 Apr 2016 14:52:15 GMT

Is the SQL Server client and server configured for TCP/IP? If not, trying to connect over an IP address will generate an error.

Re: Can't Connect to new SQL Server Instance with SQL Manager

JoeBorgione — Tue, 26 Apr 2016 16:15:35 GMT

As a follow up for those who may encounter a similar situation, if you are unable to connect to a Sql Server instance via the ip address of the instance machine, take a look at this: I can't connect to my servers SQL database via an IP Address - Database Administrators Stack Exchange it includes the following procedure on how to ensure tcp/ip connectivity:

Re: Can't Connect to new SQL Server Instance with SQL Manager

GailWallace — Thu, 19 May 2016 20:17:55 GMT

I would

1. make sure to unhide known file extensions

2. make an empty notepad *.txt file on your desktop

3. change the extension to *.UDL

4. Tab to connection

5. enter new server name

6. see if using Windows Security you can see the database in (3.) pull window otherwise it is a rights issue.