topic Re: Spatial Database Connection Failed with new users as read only in Data Management Questions

Spatial Database Connection Failed with new users as read only

KenMarshall1 — Tue, 13 Mar 2012 12:55:37 GMT

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

Re: Spatial Database Connection Failed with new users as read only

EoinRyan — Tue, 13 Mar 2012 20:00:13 GMT

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

Re: Spatial Database Connection Failed with new users as read only

KenMarshall1 — Wed, 14 Mar 2012 11:28:08 GMT

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

Re: Spatial Database Connection Failed with new users as read only

JakeSkinner — Wed, 14 Mar 2012 11:52:43 GMT

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.

Re: Spatial Database Connection Failed with new users as read only

KenMarshall1 — Wed, 14 Mar 2012 12:50:14 GMT

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.

Re: Spatial Database Connection Failed with new users as read only

JakeSkinner — Wed, 14 Mar 2012 13:06:09 GMT

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?

Re: Spatial Database Connection Failed with new users as read only

KenMarshall1 — Wed, 14 Mar 2012 13:25:58 GMT

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

Re: Spatial Database Connection Failed with new users as read only

EoinRyan — Wed, 14 Mar 2012 17:03:58 GMT

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.

Re: Spatial Database Connection Failed with new users as read only

KenMarshall1 — Wed, 14 Mar 2012 17:30:38 GMT

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.

Re: Spatial Database Connection Failed with new users as read only

DeminHu — Mon, 19 Mar 2012 12:17:40 GMT

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?

Re: Spatial Database Connection Failed with new users as read only

KenMarshall1 — Tue, 20 Mar 2012 11:36:05 GMT

Yes we have. We have moved to direct support from ESRI and have work 3 hours on it via phone and still unable to connect. We going back at it today. I will post the fixed if we find out what is wrong. We are just baffled including ESRI support. Cause all the owners work great it is just the data readers we can't get to work.

Any post would help even if you thing we already checked.

Re: Spatial Database Connection Failed with new users as read only

DeminHu — Tue, 20 Mar 2012 12:54:19 GMT

If you fixed, please let us know waht the problem is.

Actually after read your latest post, I created a login and added to a sde database as datareader, I didn't have any problem to login.

Re: Spatial Database Connection Failed with new users as read only

KenMarshall1 — Tue, 20 Mar 2012 14:54:58 GMT

Interesting, can you grant and revoke privileges? And test to see if it works. When we grant or revoke privileges it doesn't change anything.

We have a conference call today at 1pm (EST) with ESRI support team.

We did come across an issue of not being able to see who have what privileges easily, but I guess that will be new in 10.1 release.

Re: Spatial Database Connection Failed with new users as read only

DeminHu — Tue, 20 Mar 2012 15:59:14 GMT

I created a test user login and added to sde database as datareader, I didn't have aby problem to connect, but I do have problem with privileges as you said, it doesn't change anything.

In our database, we do have very complicated logins, data owner, read-only users, windows logins, and database users, we don't have problem with privileges, all users has right right to access specific data.

we don't set a user in datareader role from database level, because in this case, the user could and should read all data.

Good luck !

Re: Spatial Database Connection Failed with new users as read only

KenMarshall1 — Tue, 20 Mar 2012 18:55:52 GMT

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."

Re: Spatial Database Connection Failed with new users as read only

DeminHu — Tue, 20 Mar 2012 19:58:18 GMT

Sorry, I am just curious about your problem, what you mean 'defaulted as public' , we created all logins defaulted as public, we didn't have this issue, we are using SQL server too.

Thanks for sharing your experiences.

Re: Spatial Database Connection Failed with new users as read only

JoeBorgione — Tue, 20 Mar 2012 20:50:51 GMT

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.

Just a thought; could this have been avoided using database authentication? For SDE, I'm not a big fan of windows authentication which is why I ask. I've always used database authentication.

Re: Spatial Database Connection Failed with new users as read only

KenMarshall1 — Wed, 21 Mar 2012 12:04:07 GMT

Either way we did it database authentication and operating system authentication we could not connect. We could connect if we put the user as owner but not data reader. And the change privilage tool did nothing but it didn't error out neither and you could see the changes in the securable is SQL. And in the SQL log the users connection succeeded. So, we were just baffled.

After watching him on my screen and bringing in other ESRI support people I saw things I never seen before with tracing scripts and such. All I know it was a big issue which could happen to anyone. Here is what the ESRI support e-mailed me after the incident was fixed. They had to go into every index and table and grant permissions manually to each one. That alone took 40 minutes.

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.

I hope this anwser your questions. If you have this issue you can reference the incident number I provided above.

I'll check back to see if anyone needs for info.

Re: Spatial Database Connection Failed with new users as read only

KenMarshall1 — Wed, 21 Mar 2012 14:24:32 GMT

Well not over yet. I have two other database having the same issue to the probably exist in each database.

Here we go again at least they know what it is this time.

Re: Spatial Database Connection Failed with new users as read only

ConstanceDavisson — Thu, 22 Mar 2012 00:36:00 GMT

Well not over yet. I have two other database having the same issue to the probably exist in each database.

Here we go again at least they know what it is this time.

oh my god,You're really in trouble