2

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.

  • The lack of connection properties means that the connection failed. Please edit the question to specify the version of ArcGIS and the version of SQL Server. – Vince Feb 26 '18 at 20:20
  • Also, can you access the data within the SDE connection you just created via ArcMap or Catalog? My suspicion is that you can, and you are only having trouble accessing data via standalone scripting. – AlecZ Feb 26 '18 at 21:28
  • It's been ages since I last used SQL Server, but back then the switch for just OS/just database/mixed logins was quite prominent. – Vince Feb 27 '18 at 12:43
  • @AlecZ - Yes, I can access it via ArcMap/Catalog. Not sure why I can't with standalone scripting. – MKF Feb 27 '18 at 14:14
2

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): enter image description here enter image description here enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.