nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Encuentra más publicaciones etiquetadas con

Accepted answers

KenMarshall1

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

All comments

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

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.

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.

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.

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.

KenMarshall1

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

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

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

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

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?

KenMarshall1

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.

user-mappings.jpg

Connect Properties.jpg

EoinRyan

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

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

jskinner

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

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.

jskinner

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

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

EoinRyan

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

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.