Spatial Database Connection Failed with new users as read only

KenMarshall1 · ‎03-13-2012

Hello,

I'm running Arcserver SDE and SQL server 2008 R2 standard. I have my databases setup and can connect using "sde", "sa" and myself using operating system authentication. Of course all three are owners of the databases.

I go to set up another user just like I did with myself but as a database reader not owner and can't connect and get the error "Failed to connect to the specified server operation failed. But, when I change the new user to database owner he can connect just fine.

Also, when we try to connect the user as a database reader we can see under the SQL server Logs we can see "Login succeeded for user domain\user Connection made using Windows Authentication" But, we get the error "Failed to connect to the specified server operation failed" in the spatial database connection window "test connection"

Things I have tried consist of granting the users privileges using the Change Privieges tool and under the verision manger added the user which not sure if this even matters at the station cause were just try to get connection to the SDE/geodatabase

Any help would be great just to get pasted the connect error to the database

Thanks
Ken

KenMarshall1 · ‎03-20-2012

WOW! We GOT IT! After 2 days and 7 hours of phone support it ended up being an issue with the public rolls. As each new users is added they get defaulted as public. Well all the indexes, tables etc... had no permissions under public so we had to grant all that under public manually (long process). How this happened they don't know. This issue does come up in Oracle (Allot) but this is the first time in SQL.

From ESRI support team:

"I am following up on incident(#1019662). As we discussed over the phone, the PUBLIC role in the database was corrupted and after fixing the PUBLIC role we were able to successfully connect to the database using windows authenticated users and grant them required permissions."

View solution in original post

EoinRyan · ‎03-13-2012

Hello,

I'm running Arcserver SDE and SQL server 2008 R2 standard. I have my databases setup and can connect using "sde", "sa" and myself using operating system authentication. Of course all three are owners of the databases.

I go to set up another user just like I did with myself but as a database reader not owner and can't connect and get the error "Failed to connect to the specified server operation failed. But, when I change the new user to database owner he can connect just fine.

Also, when we try to connect the user as a database reader we can see under the SQL server Logs we can see "Login succeeded for user domain\user Connection made using Windows Authentication" But, we get the error "Failed to connect to the specified server operation failed" in the spatial database connection window "test connection"

Things I have tried consist of granting the users privileges using the Change Privieges tool and under the verision manger added the user which not sure if this even matters at the station cause were just try to get connection to the SDE/geodatabase

Any help would be great just to get pasted the connect error to the database

Thanks
Ken

Hi Ken,

Test if the OS user can log using MS Management Studio.
The steps to add windows authenticated logins are here: Adding Windows-authenticated logins or groups to a SQL Server database

KenMarshall1 · ‎03-14-2012

Hi ,

Yes the OS user can log in to MS visual studio. His security rolls are public and he has reader access to the database. But, still can't connect via spatial database connection as reader but can as owner.

We followed the directions you post.

I�??m willing to post screen shots or whatever to get this issue fixed. I got myself as a GISP and my IT department working on this and we are just baffled.

Thanks
Ken

JakeSkinner · ‎03-14-2012

Can you try creating a 'test' database user and attempt to make a connection. Here is article that will work you through the steps:

http://support.esri.com/en/knowledgebase/techarticles/detail/34639

The article is for SQL Server 2005, but it will be the same for 2008.

KenMarshall1 · ‎03-14-2012

The test didn't work. Spatial database connection did not connect still but in the SQL log says he succeeded log in. And he can still log in as owner but not as reader. So no change.

JakeSkinner · ‎03-14-2012

How are you attempting to connect to the SDE geodatabase, direct connection (i.e. sde:sqlserver:<server>) or 3-tier (i.e. 5151)?

What database roles, server roles, and permissions does the user have?

KenMarshall1 · ‎03-14-2012

How are you attempting to connect to the SDE geodatabase, direct connection (i.e. sde:sqlserver:<server>) or 3-tier (i.e. 5151)?

What database roles, server roles, and permissions does the user have?

Everything is direct connect
User has data reader on the database and server roll is public

We gave him permission of "grant" using the change privilege tools on the feature class within the feature dataset

owner scheme with domain\username

Thanks
Ken

EoinRyan · ‎03-14-2012

Everything is direct connect
User has data reader on the database and server roll is public

We gave him permission of "grant" using the change privilege tools on the feature class within the feature dataset

owner scheme with domain\username

Thanks
Ken

Post a screenshot of the User Mapping under the SQL Server login properties.
and a screenshot of your ArcCatalog spatial connection properties.

KenMarshall1 · ‎03-14-2012

I uploaded the screen shots as attachments. Let me know if they didn't come in. We like to just sign in using Operating system authentication which works as long as everyone is an owner of the database.

DeminHu · ‎03-19-2012

Did you login to sql manager studio to check if the user and schema are really in your database (Security -> users or Schemas )? Or make a OLE DB connection to your database from ArcCatolog with the user?