Can't find feature classes when creating SDE connection file with credentials

Question

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

Using ArcGIS 10.5. SQL Server 2014, version 12.0.5207.0 (64 bit)

I am using the CreateDatabaseConnection geoprocessing tool to create an SDE connection file and list feature classes within it. I am using this tool instead of the more typical Database Connections\myDB.sde because I need to supply a username and password for connection by external users when this project is published up on a server and people run this script via a web interface. They won't have my windows authentication, thus the need for database authentication.

When using this script, I receive an exit code of 0 but it will not list the feature classes that I know exist in the database. I can see that it creates the SDE connection file. I have given the database user read and write membership using SQL Server Management Studio, so I'm not sure if it's a permissions issue.

import arcpy

arcpy.CreateDatabaseConnection_management(out_folder_path='C:\Users\xxx\Desktop\GIS_Testing', out_name='HBM.sde', database_platform='SQL_SERVER',
                                          instance='sde:sqlserver:myServerName', account_authentication='DATABASE_AUTH', username='HBMWriter', password='xxx',
                                          save_user_pass='SAVE_USERNAME', database='HBMTest')
arcpy.env.workspace = r'C:\Users\xxx\Desktop\GIS_Testing\HBMTest.sde'
featClasses = arcpy.ListFeatureClasses()
for fc in featClasses:
    print fc

It's worth noting that when I try to get some workspace properties on this SDE connection file, I get back: AttributeError: DescribeData: Method connectionProperties does not exist.

If I try to access connectionProperties of the regular Database Connections\myDB.sde it'll give me the properties so it's as if it's not treating the connection file as a workspace.

Answer 1

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