topic Re: Cannot Connect to SQL in Data Management Questions

Cannot Connect to SQL

TimHayes — Fri, 07 Sep 2012 17:12:16 GMT

My situation:

Installed on my 64-bit Windows Server 2008 Server:
- SQL Server Express 2008 R2
- ArcSDE 10.1 for SQL
- ArcGIS Server 10.1 for Enterprise (Advanced)

Installed on my 32-bit Windows 7 Desktop:
- ArcGIS Desktop 10.1 for Advanced (ArcInfo)
- SQL Server 2008 R2 Client

In MS SQL Server Management Studio I have created the database. In addition, I already created the instance during installation.
There is no data in the database as of yet, but I did create it in studio. My Authentication is Windows (Operating System).

Problem:
From my desktop, in ArcCatalog, I go to Database Connections:
Database Platform: SQL Server
Instance: PGIS
Authentication Type: Operating System Authentication
Database: PGIS1

Connection Pop Up Box says: "Unable to connect to the database server to retrieve database list; please specify your server name, user name, and password info again. Failure to access the DBMS server."

Being new to SQL Server and ArcSDE, I think I might have missed something.

Re: Cannot Connect to SQL

JakeSkinner — Fri, 07 Sep 2012 17:20:36 GMT

Hi Timothy,

1. Does your window's account have 'Connect' privileges to the database?

2. Do you know if this is a named instance for SQL Server? If it is, you will want to change the instance to, for example:

<server name>\PGIS

Re: Cannot Connect to SQL

TimHayes — Fri, 07 Sep 2012 17:31:16 GMT

Hi Timothy,

1. Does your window's account have 'Connect' privileges to the database?

2. Do you know if this is a named instance for SQL Server? If it is, you will want to change the instance to, for example:

<server name>\PGIS

I have Admin Rights to the Server and Desktop. In SQL Server Management Studio, under Permissions, it shows I am the "sa" (Server Administrator?). In case I am doing something wrong, can you briefly explain to me how to verify if I have connect privileges to the database? I am getting to know my way around Management Studio so this should make it a little easier for your explanation.

I tried using<server name>\PGIS and still not able to connect.

Re: Cannot Connect to SQL

JakeSkinner — Fri, 07 Sep 2012 17:35:35 GMT

If you are the sa, then you have connect privileges. Is PGIS the name of the server where SQL Server is installed?

Re: Cannot Connect to SQL

TimHayes — Fri, 07 Sep 2012 17:38:09 GMT

If you are the sa, then you have connect privileges. Is PGIS the name of the server where SQL Server is installed?

Server Name: PGISNEW
Instance Name: PGIS
Database Name: PGIS1

In SQL Server Management Studio, the Server Type is Database Engine.

Re: Cannot Connect to SQL

JakeSkinner — Fri, 07 Sep 2012 17:46:49 GMT

You can double-check to make sure your window's account has the correct permissions by right-clicking on the database in Management Studio > Properties > Permissions. Select your windows account and choose the 'Effective' tab.

Your database connection dialog should look like:

[ATTACH=CONFIG]17554[/ATTACH]

Re: Cannot Connect to SQL

TimHayes — Fri, 07 Sep 2012 17:56:11 GMT

You can double-check to make sure your window's account has the correct permissions by right-clicking on the database in Management Studio > Properties > Permissions. Select your windows account and choose the 'Effective' tab.

Your database connection dialog should look like:

[ATTACH=CONFIG]17554[/ATTACH]

It appears I do not have permission to connect to this database. The Users/Roles are empty. When I click the Search Button all I get are Guest and Public as the only to options for Users/Roles. I try to add my name <domain>\Timothy.Hayes and it says my name cannot be found.

How do I add Users/Roles to my database? All I want to do is add myself as the users in the role of administrator.

Re: Cannot Connect to SQL

JakeSkinner — Fri, 07 Sep 2012 18:05:10 GMT

Go to your Security folder, not under your database, but under the instance. Then expand Logins. Is your windows account listed there? If it is, right-click on it > Properties > Server Roles. Is 'sysadmin' and 'public' checked? If it is, you won't have to add your windows account to the database. The 'sysadmin' role gives your account administrative privileges over the entire instance (including all databases).

If your windows account is not listed under Logins, right-click on the Logins folder > New Login. Click the Search button and make sure 'From this location' is pointing to your network, and not the current server. You should then be able to add your login, and then grant it 'sysadmin' under Roles at the top left.

Re: Cannot Connect to SQL

TimHayes — Fri, 07 Sep 2012 18:11:50 GMT

Go to your Security folder, not under your database, but under the instance. Then expand Logins. Is your windows account listed there? If it is, right-click on it > Properties > Server Roles. Is 'sysadmin' and 'public' checked? If it is, you won't have to add your windows account to the database. The 'sysadmin' role gives your account administrative privileges over the entire instance (including all databases).

If your windows account is not listed under Logins, right-click on the Logins folder > New Login. Click the Search button and make sure 'From this location' is pointing to your network, and not the current server. You should then be able to add your login, and then grant it 'sysadmin' under Roles at the top left.

My account is there. 'sysadmin' and 'public' are both checked. Should I add 'public' under the database permissions and then try and connect via ArcCatalog?

Re: Cannot Connect to SQL

TimHayes — Fri, 07 Sep 2012 18:16:10 GMT

My account is there. 'sysadmin' and 'public' are both checked. Should I add 'public' under the database permissions and then try and connect via ArcCatalog?

I just tried to re-connect from ArcCatalog. Same message as before, cannot make a connection to the database. I have always been able to connect to the server and my ArcGIS Server Services. Not sure if this info is helpful.

Re: Cannot Connect to SQL

JakeSkinner — Fri, 07 Sep 2012 18:25:52 GMT

1. Check to make sure 'SQL Server Browser' is running within your services

2. Go to Start > Programs > SQL Server > Configuration Tools > SQL Server Configuration Manager. Expand SQL Server Network Configuration > Protocols for MSSQLSERVER. Check to make sure Named Pipes and TCP/IP are both enabled. Check the same for SQL Server Native Client Configuration within the Configuration Manager.

3. By default, SQL Server communicates through port 1433. Do you have this port open within your Firewall (if there is one)?

Re: Cannot Connect to SQL

TimHayes — Fri, 07 Sep 2012 21:36:49 GMT

1. Check to make sure 'SQL Server Browser' is running within your services

2. Go to Start > Programs > SQL Server > Configuration Tools > SQL Server Configuration Manager. Expand SQL Server Network Configuration > Protocols for MSSQLSERVER. Check to make sure Named Pipes and TCP/IP are both enabled. Check the same for SQL Server Native Client Configuration within the Configuration Manager.

3. By default, SQL Server communicates through port 1433. Do you have this port open within your Firewall (if there is one)?

I did #2 they are both enabled. How do I get to #3?

Also, in SQL Server Management Studio - Database Properties for PGIS - Permissions - Users/Roles; the white space is empty. I go to Search and it gives me the option of choosing guest or public. When I type in my <doman>/timothy.hayes it says it cannot locate the user. I then choose public, it accepts it, but then when I exit Studio, and re-open, pubic is gone and there is nothing in the previously mentioned white space.

Re: Cannot Connect to SQL

TimHayes — Fri, 07 Sep 2012 22:33:21 GMT

I have ArcGIS Desktop loaded on my Server. I am able to connect to the SQL Server Database in ArcCatalog.

But, on the client side, my own PC, I cannot make the connection. I think it may have something to do with the SQL Server Native Client I installed. When I installed it on the client side, it did not give me an option to do any configuration. Now that I provided this info, any ideas on why I cannot connect from the Client side? but I can connect from the Server side using ArcCatalog.

Re: Cannot Connect to SQL (SOLUTION FOUND!)

TimHayes — Mon, 10 Sep 2012 04:49:26 GMT

I am now able to connect to my Enterprise SQL Server Express Database from the Client side in ArcCatalog. Here is how I did it:

On my Server, I opened SQL Server Configuration Manager, then under SQL Server Services I opened SQL Server Browser and Started it.

It appears that if SQL Server Browser is Stopped or otherwise disabled, you cannot connect from the Client Side.

All is well now.

Can I give myself a Point for finding the answer? 🙂

Re: Cannot Connect to SQL

VinceAngelo — Mon, 10 Sep 2012 10:21:29 GMT

Shouldn't the check chould go to Jake for point #1 on Friday (4 posts ago)?

- V