topic Cannot create connection string from .SDE file in ArcGIS API for Python Questions

Cannot create connection string from .SDE file

Anonymous User — Sun, 12 Dec 2021 10:14:28 GMT

Hello,

I'm working on a script to read all the SDE connections from my Pro project to check whether this datasource is registered with my Arcgis Server. If the datasource is not registered, I want to fix this by adding the connection to Arcgis Server.

This script should avoid copying the data to Arcgis Server from my Enterprise Geodatabase when I publish a mapservice from an Arcpy Script.

I found the following functions in the Python documentation:

https://esri.github.io/arcgis-python-api/apidoc/html/arcgis.gis.server.html#arcgis.gis.server.DataStoreManager.generate_connection_string

https://esri.github.io/arcgis-python-api/apidoc/html/arcgis.gis.server.html#arcgis.gis.server.DataStoreManager.add_database

However, my script gives an error when I run the generate_connection_string function on the datastoremanager object.

The datastoremanager object works fine when I list the datasources which are registered with Arcgis Server

I'm using Arcgis Pro 2.2.4 with arcgis python package 1.4.1 and Arcgis Enterprise 10.6.1

The following script gives the error creating the connectionwhen I run it from the Python console in Pro:

import arcgis.gis
import arcgis.gis.server
gis = arcgis.gis.GIS(url='https://esri.joelhempenius.nl/portal/', username='portaladmin',password='fakepassword')
server = arcgis.gis.server.Server(url='https://esri.joelhempenius.nl/server', gis=gis)
datastoremanager = server.datastores
datastoremanager
<DataStoreManager for https://esri.joelhempenius.nl/server/admin/data>
datastoremanager.list()
[<Datastore title:"https://esri.joelhempenius.nl/server/admin/data/items/enterpriseDatabases/AGSDataStore_ds_dxwu2ulk" type:"egdb">, <Datastore title:"https://esri.joelhempenius.nl/server/admin/data/items/enterpriseDatabases/ov" type:"egdb">, <Datastore title:"https://esri.joelhempenius.nl/server/admin/data/items/enterpriseDatabases/ov_pro" type:"egdb">, <Datastore title:"https://esri.joelhempenius.nl/server/admin/data/items/enterpriseDatabases/sde_ov@gis_t" type:"egdb">, <Datastore title:"https://esri.joelhempenius.nl/server/admin/data/items/nosqlDatabases/AGSDataStore_nosqldb_tcs_1hpjd8nn" type:"nosql">]
datastoremanager.generate_connection_string(r'C:\SDE\sde_ov@gis_t.sde')
Traceback (most recent call last):
 File "<string>", line 1, in <module>
 File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\arcgis\gis\server\admin\_data.py", line 315, in generate_connection_string
 if self._con.portal_connection:
AttributeError: '_ArcGISConnection' object has no attribute 'portal_connection'‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I also tried server = arcgis.gis.server.Server(url='https://esri.joelhempenius.nl/server/admin', gis=gis), but this gives me the same error.

Any ideas why I get this error?

Re: Cannot create connection string from .SDE file

JarrettGreen — Sun, 12 Dec 2021 10:14:31 GMT

I'm running into the same problem. Below is my DataStoreManager object I am creating. Did you ever find a resolution to this?

dsm = server.DataStoreManager(ags_admin_url + '/admin/data', gis=gis)
conn_str = dsm.generate_connection_string(sde)

{AttributeError}'_ArcGISConnection' object has no attribute 'portal_connection'

Re: Cannot create connection string from .SDE file

JoëlHempenius3 — Wed, 08 May 2019 18:31:18 GMT

I discussed the issue with a Esri Developer at the DevSummit in Palm Springs.

there are two problems, the first is at line 4 in my original code sample and line 2 in the sample below. As it turns out, you should not create your ArcGIS Server object this way, but instead get it from the Gis object, which contains a list of all federated servers in your ArcGIS Enterprise. However, the Gis object only contains the public URLS from the Federated ArcGIS Servers and if you disabled Administrative Access on the webadaptor, you cannot execute the publishing tools necessary for this operation.

If administrative access is enabled, get your ArcGIS Server from the GIS object, if it is not, or you run into some other issue, here is my workaround:

The example below should have Gis object (self gis), an ArcGIS Server url with administrative access and in this case I loop my Pro project layers to check if all database connections are there. The workaround is in the ConvertSDEtoConnectionString function, where the SDE file is uploaded and a connection string is generated from that upload. This function is actually copied from the ArcGIS API for Python and modified to fix the failing upload.

def RegisterDataSources(self,aprx,serverurl):
        gisserver = arcgis.gis.server.Server(url=serverurl , gis=self.gis) ##this is wrong!
        #servers = gis.admin.servers.list()
        registeredDatasources = gisserver.datastores.list()
        firstmap = aprx.listMaps("*")[0]
        for lyr in firstmap.listLayers("*"):
            if lyr.supports('DATASOURCE'):
                user =lyr.connectionProperties['connection_info']['user']
                database = lyr.connectionProperties['connection_info']['database']
                datasourcename = user + '@' + database
                found=False
                for registeredDatasource in registeredDatasources:
                    path = registeredDatasource.properties('path')
                    if path.endswith(datasourcename):
                        logging.info("Datasource already registered: " + datasourcename)
                        found = True
                if not found:
                    self.RegisterDataSourcefromAprx(lyr,datasourcename,gisserver)

    def RegisterDataSourcefromAprx(self, lyr, datasourcename,gisserver):
        path = lyr.dataSource[:lyr.dataSource.rfind('\\')]
        connectionString = self.ConvertSDEtoConnectionString(path,gisserver)
        gisserver.datastores.add_database(datasourcename,connectionString)
        logging.info("Registered new datasource: " + datasourcename)

    def ConvertSDEtoConnectionString(self, path, gisserver):
        from arcgis.gis.server.catalog import ServicesDirectory
        from arcgis.gis.server import Uploads

up = Uploads(url=gisserver.url.replace("admin", "admin/uploads"),
gis=gisserver._gis)

d = ServicesDirectory(url=self.configparams['serverurl'])
d._con = gisserver._con

        try:
            service = d.get("PublishingTools", 'System')
        except:
            service = d.get("PublishingToolsEx", 'System')
        upload_res = up.upload(path=path)
        if upload_res[0] == True:
            res = service.get_database_connection_string(
                in_conndatatype="UPLOADED_CONNECTION_FILE_ID",
                in_inputdata=upload_res[1]['item']['itemID'])
            up.delete(item_id=upload_res[1]['item']['itemID'])
        return res

Re: Cannot create connection string from .SDE file

DallasShearer2 — Mon, 18 Mar 2024 17:59:45 GMT

Using the python API is still problematic when trying to create a connection string from a database connection. As well as trying to register the connection with your Server.

JoëlHempenius3 post above does work. Here's another example of using the same methods he used.

servers_dict = portal_conn.admin.federation.servers host_server = next( (_server for _server in servers_dict["servers"] if _server["serverRole"] == 'HOSTING_SERVER')) host_admin = portal_conn.admin.servers.get(role="HOSTING_SERVER")[0] data_store_manager = host_admin.datastores uploader = Uploads(url=f'{host_admin.url}/uploads', gis=host_admin._gis) uploaded_connection_file = uploader.upload(path=path_to_connection_file) service_directory = ServicesDirectory(url=host_server['url']) service_directory._con = host_admin._con publishing_service = service_directory.get("PublishingTools", "System") connection_string = publishing_service.get_database_connection_string(in_conndatatype='UPLOADED_CONNECTION_FILE_ID', in_inputdata=uploaded_connection_file[1]["item"]['itemID']) uploader.delete(item_id=uploaded_connection_file[1]['item']['itemID']) item_parameter = {"info": {"isManaged": False, "dataStoreConnectionType": "shared", "connectionString": connection_string}, "type": "egdb", "path": "/enterpriseDatabases/my_egdb_datastore"} result = data_store_manager.add("my_egdb_datastore", item_parameter) print(f'Added Datastore: {result}')