topic Re: Create Database Connection - can't find the feature classes in Python Questions

Create Database Connection - can't find the feature classes

MKF62 — Sat, 11 Dec 2021 17:30:18 GMT

EDIT: When I try to access the workspace properties of the connection file, it says that the method workspaceType doesn't exist for describing data. When I use the regular 'Database Connections\myDB.sde' connection I can access the workspace properties just fine. When I run desc.dataType on it it comes back as "File"...how do I make it a workspace? The connection file is obviously not being treated as a workspace, and that's the first issue..

I'm getting an exit code of 0 and no errors when I do this, but it's not finding any feature classes so it's not working as I expect it to.

The problem is that I'm creating a connection file using database authentication, setting the workspace to that connection file, then trying to list the feature classes. I get nothing returned. My first thought is that it's a permissions issue but the database user being used for authentication has read and write access, so now I'm not sure what the deal is.

import arcpy

arcpy.CreateDatabaseConnection_management(r'C:\Users\xxx\Desktop\GIS_Testing', 'HBMTest.sde', 'SQL_SERVER',
                                          'sde:sqlserver:myServerName', 'DATABASE_AUTH', 'HBMWriter', 'xxx',
                                          'SAVE_USERNAME', 'myDBName')

arcpy.env.workspace = r'C:\Users\xxx\Desktop\GIS_Testing\HBMTest.sde'
featClasses = arcpy.ListFeatureClasses('*')
for fc in featClasses:
    print fc‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

When I use the code below, it works and will list the feature classes, but the purpose of using the "CreateDatabaseConnection" and passing in credentials is because eventually this script will be published up on a server where external users will be running it and they won't have my windows authentication to access the database, so I really need to make sure that the above is working.

arcpy.env.workspace = r'Database Connections\HBMTest.sde'
featClasses = arcpy.ListFeatureClasses('*')
for fc in featClasses:
  print fc‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Am I missing some silly thing in arcpy or is this more likely a permissions issue? These are the permissions set in SQL Server Mgmt Studio for the user:

Re: Create Database Connection - can't find the feature classes

ThomasColson — Mon, 26 Feb 2018 20:07:22 GMT

Just for stuff and giggles, do you get the same error when the user is mapped to the DBO schema? if so, despite the reader writer mappings, your user still has no access to the table, which you'll have to assign explicitly.

Re: Create Database Connection - can't find the feature classes

MKF62 — Mon, 26 Feb 2018 20:14:31 GMT

Thanks for the response - I'm pretty new to the this stuff, could you explain how to assign that explicitly? The user is mapped to the DBO schema, so maybe that is the problem.

Re: Create Database Connection - can't find the feature classes

George_Thompson — Mon, 26 Feb 2018 20:39:20 GMT

That might cause an issue having a different user name and schema. In the help documentation, we say that the schema and username must match;

All database users who will create data must have a schema in the database. That schema must have the same name as the user name.

Add logins and users to SQL Server—Help | ArcGIS Desktop

Unless that user is granted db_owner (database level) or SYSADMIN (server level) privileges, I would think that error may happen.

What happens when you update the schema to: HabitatTestWriter and keep the permissions the same?

https://community.esri.com/groups/geodatabase?sr=search&searchId=341e0503-7c05-4bb0-a238-fb55e87ebb01&searchIndex=0‌

Re: Create Database Connection - can't find the feature classes

MKF62 — Mon, 26 Feb 2018 20:48:33 GMT

Nothing happens. Still no feature classes are listed.

Do I possibly need to use the Change Privileges geoprocessing tool to assign view/edit privileges to the various tables in the database? Just found this tool, have never used it (this is my first time setting up an enterprise geodatabase).

Re: Create Database Connection - can't find the feature classes

George_Thompson — Mon, 26 Feb 2018 20:52:37 GMT

I would. It will assign the permissions correctly to all the required objects. Run it as the data owner.

Re: Create Database Connection - can't find the feature classes

MKF62 — Mon, 26 Feb 2018 20:57:37 GMT

Still getting nothing after doing this...

Re: Create Database Connection - can't find the feature classes

George_Thompson — Mon, 26 Feb 2018 20:59:28 GMT

I am not sure, without looking at it in "person".

You may want to call into Esri Technical Support and have an analyst take a look at it. That could be the quickest way.

Re: Create Database Connection - can't find the feature classes

MKF62 — Tue, 27 Feb 2018 12:51:09 GMT

So I deleted the user and recreated it from the SSMS side. Now I can create a connection with it in both ArcCatalog and SSMS. The problem remains that the connection cannot be made in standalone scripting however.

Re: Create Database Connection - can't find the feature classes

MKF62 — Sat, 11 Dec 2021 17:30:20 GMT

Alright, after much tinkering, I finally got it to work. It took two things:

1) I had to create the User through SQL Server Management Studio instead of using the various geoprocessing tools. I'm still not entirely sure why, but it worked. First (as db_owner) I created a new login (Security > Logins (right-click) > New Login > fill out username, password fields and check appropriate boxes. Then I created the user in the database of interest and created a schema by the same name and assigned it to the user (Database of interest > Security > Users (right click) > New User... and Database of Interest > Security > Schemas (right-click) > New Schema...). Then I tested this connection in ArcCatalog. The user should connect but since it doesn't have any data privileges yet, it won't be able to see any data in the database. When I determined that connection was successful, I tested the connection in SSMS; again it should connect but you shouldn't be able to see any of your feature classes or tables since the user has no privileges yet. Next, I went to Catalog and added an "Editor" role. Steps to do this include creating a DB connection using my db_owner user (my windows authentication in this case), right-click the connection > Administration > Create and Manage Roles. Filled out with "Editor", GRANT, and the name of the user I just created in SSMS. I then went to each feature class and right clicked > Manage > Privileges > Add > "Editor". Then I selected all checkboxes for Select, Insert, Update, Delete. Clicked Ok and returned to SSMS where I connected as my db_owner account. Went to the database of interest > Security folder > Users > opened properties for my new user and made sure it looked like so (Securables and Extended Properties are empty):

2) In my python script, I removed "sde:sqlserver:" from my instance name and ran the process. This time it connected successfully to my database using my new, database-authenticated user.

import arcpy

arcpy.CreateDatabaseConnection_management(out_folder_path='C:\Users\xxx\Desktop\GIS_Testing',
                                          out_name='HBMTest.sde', database_platform='SQL_SERVER',
                                          instance='myServerName',
                                          account_authentication='DATABASE_AUTH', username='HabitatTestWriter',
                                          password='xxx',
                                          save_user_pass='SAVE_USERNAME', database='HBMTest')
arcpy.env.workspace = r'C:\Users\xxx\Desktop\GIS_Testing\HBMTest.sde'

featClasses = arcpy.ListFeatureClasses()
tables = arcpy.ListTables()
for fc in featClasses:
    print fc