Issues creating a 10.5.1 Geodatabase on Sql Server 2016

[AdamCrateau1]

Having issues when creating a GDB in Sql Server 2016.  We're trying the method where our SQL dba creates an empty database or container, and the GDB admin (aka me) enables a geodatabase using DBO schema.

Some specifics:

My client:  Desktop 10.5.1

Sql Server Native Client:  version 9.00

Authorization file:  keycodes from an ArcServer 10.2.2 instance

Sql Server: version #13.0.4435.0

Database:  Read Committed & Allow Snapshot enabled. 

DB Security:  User matching my OS creds created, and given DB_Owner role.

Steps so far:

1. In Catalog, Added a Database Connection to the empty database

2. Right-clicked database connection file, and clicked Enable Geodatabase...

3. Process errored out.  Here's the contents of the sde_setup.log:

[Fri Oct 06 12:09:21 2017] Error creating VERSION table. (-51)
[Fri Oct 06 12:09:21 2017] DBMS error code: 2760
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The specified schema name "sde" either does not exist or you do not have permission to use it.

[Fri Oct 06 12:09:21 2017] SDE schema object install not completed.
[Fri Oct 06 12:09:21 2017] ERROR installing/upgrading ArcSDE, Error = -51

Any thoughts?

We are ready to have my login added as a SysAdmin on the Sql Server, if that is the best way to create a GDB with DBO schema.

[BillFox]

Install the ODBC v13 Driver

[BillFox]

and uninstall SQL Server Native Client

[AdamCrateau1]

Thanks Bill.. did as you said and still getting the same error.  It seems to want to create the GDB using the SDE schema, or at least I don't see an option to use DBO when using this workflow.

[Asrujit_SenGupta · Accepted solution]

Privileges for geodatabases in SQL Server—Help | ArcGIS Desktop 

Grant 'sysadmin' to your OS Authenticated login and execute the tool.

Even though you have the 'DB_Owner' role, your DBA created the database, and hence you are not the actual owner for that database.

A comparison of geodatabase owners in SQL Server—Help | ArcGIS Desktop 

Check the above link to check how a non-sysadmin login can be made DBO user for a Database.

https://community.esri.com/community/gis/managing-data?sr=search&searchId=6a9454b3-db9e-45d4-a7ce-d3...‌

[AdamCrateau1]

Thank you Asrujit.  If we temporarily Grant sysadmin to my windows login, and then revoke after the GDB is created, will I still be able to perform the full range of geodatabase admin tasks (as DB_Owner)?  

[Asrujit_SenGupta]

Check this The geodatabase administrator in SQL Server—Help | ArcGIS Desktop  and the other link I provided earlier - A comparison of geodatabase owners in SQL Server—Help | ArcGIS Desktop 

In Microsoft SQL Server, the geodatabase administrator can be either a user named sde or a login that is mapped to the dbo user in the database that contains the geodatabase.

So if you temporarily grant sysadmin and then revoke after the GDB is created, then your login will be just another login.

[AdamCrateau1]

So, after being elevated temporarily to sysadmin I was able to run the Create Enterprise Geodatabase tool successfully with DBO schema.

Once the GDB was created, our Sql DBA removed my login from sysadmin, and set it back to DB_Owner.  I'm able to do all the geodatabase adminitstrative tasks I typically do within this role, e.g. compress and rebuild/analyze statistics on system tables.

[ThomasPuthusserry]

Hi I am trying to do the same as Adam Crateau: Trying to 'Enable Enterprise Geodatabase' functionality for a database hosted in the Amazon RDS (SQL Server Express Edition 13.00.2164.0.v1). I followed the steps explained here (Create a geodatabase on Amazon Relational Database Service for SQL Server—ArcGIS Enterprise on AWS |... ) but there is no success. Is this because my RDS instance is not a standard SQL Server edition ?

Any suggestions would be appreciated.

Thanks Thomas 

[AbeCoughlin1]

Thanks ESRI, for again providing documentation that is unclear...

Adam, if the dba creates it, they own it. As Asrujit states, being assigned 'dbo_owner' does not make you the owner.

In SQL Server Management Studio, open the properties of the database and find 'owner' (Database>Properties>Files) and you can change it here.

Once you've become the true owner of the database you can create the geodatabase.