Cannot Connect to SQL

[TimHayes]

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.

[JakeSkinner]

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

[TimHayes]

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.

[JakeSkinner]

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

[TimHayes]

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.

[JakeSkinner]

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]

[TimHayes]

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.

[JakeSkinner]

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.

[TimHayes]

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?

[TimHayes]

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.