topic Re: failed to connect to the specified DBMS instance(sqlserver) in Geodatabase Questions

failed to connect to the specified DBMS instance(sqlserver)

baohuachu7 — Mon, 28 Apr 2025 09:57:46 GMT

When I add an acount(not sde) with sde schema to a geodatabase, the sde account of other geodatabase can not access geodatabase. With error info :

Underlying DBMS error, exception has occured,

Failed to connect to the specified DBMS instance.
A database connection exception has occurred.C Underlying DBMS error[42000:[Microsoft][ODBC Driver 17 for SOL Serverl[SQL Server]The SELECT permission was denied on the object "'SDE_version',database swyGDB, schema sde No extended error].

Re: failed to connect to the specified DBMS instance(sqlserver)

George_Thompson — Mon, 28 Apr 2025 10:32:19 GMT

How are you adding the new account: Pro tool / via SSMS?

Can you also expand on what "with sde schema to a geodatabase" means?

From the error message, it seems that permissions on the sde schema are not public or were changed.

Re: failed to connect to the specified DBMS instance(sqlserver)

VinceAngelo — Mon, 28 Apr 2025 12:10:37 GMT

You really shouldn't have any need to create any logins that map to the sde schema, because you should never, ever load user data with the sde schema. Enterprise geodatabases actually have a requirement that the schema be the same as the login/user for any tables to be edited with ArcGIS tools (the only exception is when the geodatabase is owned by DBO, at which point it really has no security model at all). There is a Create Database User (Data Management) tool to help you create working enterprise geodatabase logins.

This has the feel of an XY Problem. What were you attempting that made creating multiple logins that mapped to the sde schema seem necessary? Maybe we can help with that problem, instead.

- V

Re: failed to connect to the specified DBMS instance(sqlserver)

baohuachu7 — Tue, 29 Apr 2025 01:31:10 GMT

Hi George,

The operation is done in SSMS. I have one acount userA belone to GDB A. And want to authorize this account to GDB B by doing as this:

In SSMS , security->logins ->select userA->properties->user mapping->check GDB B.

and in the "database role membership of GDB B" ,checked "db_datareader,writer,owner,securityadmin..." checked.

In GDB B->security->users->select UserA->properties-> in the configuration panel check the "owned schema" with "sde,...". Then in the end the above error pops up.

Now the above issue is solved by sleect UserA->user mapping->uncheck the GDB B.

Re: failed to connect to the specified DBMS instance(sqlserver)

baohuachu7 — Tue, 29 Apr 2025 01:59:28 GMT

Hi Vince,

Thank you for your help. The GDB owned by DBO is the GDB which is created using sa account connection?

If I want an user with the privilege to edit data in both GDB A and GDB B ,but not modify feature class level , what is the steps to do this?

Re: failed to connect to the specified DBMS instance(sqlserver)

KimberleeHermann1 — Thu, 01 May 2025 03:00:37 GMT

Have you tried reinstalling the ODBC Driver?

Re: failed to connect to the specified DBMS instance(sqlserver)

George_Thompson — Thu, 01 May 2025 10:51:32 GMT

I would recommend using AD groups. Add them to the SQL instance, map them to GDB A & B (connect only), then in Pro assign the editing permissions to the groups (this must be done as the feature class owner).

That should allow them to just edit data in the allowed feature classes (or related objects).

Here is some doc to help with the process:

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/user-accounts-groups.htm#ESRI_SECTION1_97D393C792C641F8B21706C3ECDCDED6

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/add-users-sqlserver.htm#ESRI_SECTION1_1EF70F32F80041FB9666781BD3D78754

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/privileges-sqlserver.htm