Append SQL Server database connection properties using ArcPy in ArcGIS Pro

Summary

By default, when creating a SQL Server database connection using the Create Database Connection geoprocessing tool in ArcGIS Pro, the Additional Properties section is not available to specify parameters such as ApplicationIntent, Encrypt, and TrustServerCertificate. This article describes the workflow to append these parameters using ArcPy and create a connection file with the required properties.

Procedure

Prerequisites to ensure SQL Server database connection properties are appended successfully:
• Create a SQL Server database in SQL Server Management Studio. Refer to Microsoft Learn: Create a database for more information.
• Install the Microsoft ODBC driver for the SQL Server as an SQL client that runs on the ArcGIS Pro machine. Refer to ArcGIS Pro: Install the ODBC driver for SQL Server for more information.

1. Create a SQL Server database connection in ArcGIS Pro. Refer to ArcGIS Pro: Connect to the database for more information. For the Authentication Type field, select Database authentication and check the Save User/Password check box.
2. Append the SQL Server connection parameters using ArcPy.
   1. On the View ribbon, click Python Window in the Windows group.
   2. In the Python window, copy and paste the following code to append the connection properties of the SQL Server database. Replace the necessary parameters.

import arcpy, os

# Additional parameters appended to the instance string
additionalparams = ";Encrypt=no;TrustServerCertificate=yes;ApplicationIntent=READWRITE"
 # Use ArcPy to append the SQL Server database additional parameters
arcpy.management.CreateDatabaseConnection(
    out_folder_path=r"C:\FOLDER NAME",  # input your folder path to store the .sde file
    out_name="testextraparamas_frompythonwithapplicationint1", # rename the folder if needed
    database_platform="SQL_SERVER", # This is based on the database type used
    instance="YOUR SQL SERVER INSTANCE" + additionalparams,
    account_authentication="DATABASE_AUTH",
    username="YOUR SQL SERVER DATABASE USERNAME",
    password="YOUR SQL SERVER DATABASE PASSWORDS",
    save_user_pass="SAVE_USERNAME", # rename if needed
    database="YOUR DATABASE NAME", # input your SQL Server database name
    schema="",
    version_type="TRANSACTIONAL",
    version="sde.DEFAULT",
)
# Display a message if additional parameters are appended successfully
sde_path = os.path.join(r"C:\FOLDER NAME", "testextraparamas_frompythonwithapplicationint1.sde")
print(f"Created: {os.path.exists(sde_path)} | Path: {sde_path}")
print("Appended params:", additionalparams.strip(";"))

The Python window below shows the example of a message displayed if the connection properties are appended successfully.

3. Connect to an existing folder.
   1. Browse to and expand the folder.
   2. Right-click the .sde file in the folder and click Connection Properties.

3. Expand Additional Properties to view the connection properties appended in Step 2.