topic Re: Issues creating a 10.5.1 Geodatabase on Sql Server 2016 in Geodatabase Questions

Issues creating a 10.5.1 Geodatabase on Sql Server 2016

AdamCrateau1 — Fri, 06 Oct 2017 21:24:12 GMT

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.

Re: Issues creating a 10.5.1 Geodatabase on Sql Server 2016

BillFox — Fri, 06 Oct 2017 21:33:43 GMT

Install the ODBC v13 Driver

Re: Issues creating a 10.5.1 Geodatabase on Sql Server 2016

BillFox — Fri, 06 Oct 2017 21:34:39 GMT

and uninstall SQL Server Native Client

Re: Issues creating a 10.5.1 Geodatabase on Sql Server 2016

AdamCrateau1 — Fri, 06 Oct 2017 22:41:46 GMT

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.

Re: Issues creating a 10.5.1 Geodatabase on Sql Server 2016

Asrujit_SenGupta — Sat, 07 Oct 2017 07:41:44 GMT

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-d34faa9100a9&searchIndex=0‌

Re: Issues creating a 10.5.1 Geodatabase on Sql Server 2016

AdamCrateau1 — Sat, 07 Oct 2017 18:22:04 GMT

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)?

Re: Issues creating a 10.5.1 Geodatabase on Sql Server 2016

Asrujit_SenGupta — Sun, 08 Oct 2017 04:23:46 GMT

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.

Re: Issues creating a 10.5.1 Geodatabase on Sql Server 2016

AdamCrateau1 — Mon, 09 Oct 2017 16:29:13 GMT

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.

Re: Issues creating a 10.5.1 Geodatabase on Sql Server 2016

ThomasPuthusserry — Mon, 19 Feb 2018 11:55:57 GMT

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 | ArcGIS Enterprise ) 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

Re: Issues creating a 10.5.1 Geodatabase on Sql Server 2016

AbeCoughlin1 — Thu, 14 Feb 2019 00:46:42 GMT

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.