Spatial Database Connection Failed with new users as read only

[KenMarshall1]

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]

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.

[DeminHu]

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.

[KenMarshall1]

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.

[DeminHu]

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 !

[KenMarshall1 · Accepted solution]

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

[DeminHu]

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.

[JoeBorgione]

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.

[KenMarshall1]

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.

[KenMarshall1]

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.

[ConstanceDavisson]

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