Database Connection - see list of databases without sysadmin

[chuckfrank]

Hi All,

During installation of 10.1, we have been having trouble viewing the dropdown list of databases on our SQL Server (ArcCatalog --> Database Connections --> Add Database Connection).  We are using Operating System Authentication.  For a specific user, if I modify their login to have the 'sysadmin' server role, the dropdown list of databases works fine, but we don't want the user to be a sysadmin.  We have them listed under the 'public' server role.  At that level the dropdown list doesn't populate and we get an error message.  We haven't seen this issue in previous installations of ArcGIS.  I'm assuming I need to modify permissions for the login, but I'm not sure how it should be modified to setup the database connection.  We'd like to allow the minimum necessary permissions.

Thanks,
Chuck

[NanaDei]

Hi Chuck,
Is this user is part of a database role or windows group on the SQL instance?
Is the issue only  specific to this windows user?
Database users and other windows users work successfully?
CONNECT privilege is granted on databases to the public database role by default. Hence, you can grant the "db_datareader" database role to the user as a minimum.

Thanks

[chuckfrank]

Hello,

It is not specific to this individual windows user.  We have installed 10.1 for another user and they also cannot view the dropdown list of databases.  One user has a SQL login and the other would default to the [Domain]\Domain Users login.  Here is a screen shot of the general domain users login properties:

[ATTACH=CONFIG]17795[/ATTACH]

It works fine for anybody that is in the sysadmin server role (two of us).  However, we need to set up 20+ other users and we don't want to make them sysadmin.  In the user mapping I tried giving them dbreader to the master database, thinking that maybe the list is generated by querying the master db, but that didn't work.  It seems like the only way to see the list is with sysadmin.  I could login (on the Database Connection dialog in ArcCatalog) with database admin and use the sa username and password for all of our users, but that seems like too much power.  Is this a potential bug?  It wasn't an issue in previous versions.

Here is the error message I get when I take myself out of the sysadmin server role:

[ATTACH=CONFIG]17796[/ATTACH]

Thanks,
Chuck

[VinceAngelo]

The list of databases is found in the 'master' database.  If your user can't access
'master', it might not be able to access the list.

But that error message was generic enough that it miight be that you don't have
the right client library installed to access the server (ArcGIS 10.1 ships with both
32-bit and 64-bit SQL-Server client libraries for this reason).

Tech Support could probably help you straighten this out.

- V

[ShannonShields]

Chuck,

In the 'Login Properties' dialog look under 'User Mapping'. Your logins must be mapped do users in databases before those databases will appear to them in a dropdown list - at minimum they need 'connect' permission to a database, which is granted automatically when they are mapped to a database.

[ATTACH=CONFIG]17812[/ATTACH]
In this example, login 'foo' does not have access to any databases. If they try to create a connection in ArcGIS, they'll get the same error message you are seeing.

Granting people access to data in SQL Server is a two-step process. Logins are given permission to connect to SQL Server, but in order to see data, they must be mapped to specific databases, then granted the ability to read and/or write objects within those databases.

See these topics for more information on creating logins & users in SQL Server:
http://resources.arcgis.com/en/help/main/10.1/#/Adding_Windows_authenticated_logins_or_groups_to_a_S...
http://resources.arcgis.com/en/help/main/10.1/#/Adding_database_authenticated_logins_to_a_SQL_Server...


-Shannon

[chuckfrank]

Thanks Vince and Shannon,

We worked with tech support for about an hour yesterday and they are continuing to work on the issue.  We tried making the sql login and user mapping updates and the result was the same.  (still nothing in the dropdown list of databases) There doesn't seem to be any way around having the login fall into the sysadmin role.  In our case, we actually went into active directory and made the user a member of DomainAdmin, but that only worked because under operating system authentication, it dropped them into a higher level login that had sysadmin.  When ESRI responds with a course of action for out incident, I will post back on this thread.  Thanks for your recommendations.

Chuck

[HemantAngarkar2]

Hi Chuck,

Could you find solution to the problem you were having. Kindly post it.

Hemant

[chuckfrank]

Hi Hemant,

On the particular SQL database we were trying to access there was a custom role that was deny datareader.  To get around the issue we set up a database user and had the users connect with SQL authentication instead of Windows Authentication.  Due to other applications that use that database we abandoned accessing this particular database with Windows Authentication from ArcGIS.  The user has roles of public and db_datareader.

Thanks,
Chuck