The following list summarizes the steps required to connect from ArcGIS to a Microsoft SQL Server database, including a database that contains a geodatabase:
- Configure the SQL Server instance to allow connections from remote machines.
- Install a Microsoft ODBC driver on the ArcGIS client machines.
- Create a database connection.
- To publish ArcGIS Server web services that reference the data in the SQL Server database, register the database connection file with your ArcGIS Server sites.
Configure the instance to allow connections
By default, SQL Server instances are not configured to allow connections from other computers. If you have a new installation of SQL Server, ensure the SQL Server service is running and listening on the correct ports. See the Microsoft SQL Server documentation for more information.
If you are not the database administrator, contact the database administrator if you receive errors when connecting that indicate the instance is not accepting connections.
Additional configuration is required on the SQL Server and client machine to use encrypted connections.
Install the ODBC driver for SQL Server
Obtain the 64-bit Microsoft ODBC driver for SQL Server from the Microsoft Download Center or from My Esri. Always use the Microsoft ODBC driver that is supported for the version of SQL Server to which you want to connect. Install the driver on all computers where ArcGIS Pro and ArcGIS Server are installed.
If you do not have the privileges to install software on the machines where ArcGIS clients are running, you'll need to have your IT department install and configure the Microsoft ODBC driver for you.
Note:
If ArcGIS Server and SQL Server are installed on the same server, a separate ODBC driver is not required on that machine.
When connecting from ArcGIS Server on Ubuntu to a supported version of SQL Server, you must install the Microsoft unixodbc-dev package on all ArcGIS Server machines in addition to the ODBC driver.
Connect to the database
You can use the Database Connection dialog box in ArcGIS Pro to connect to the database, as described below.
Alternatively, you can run the Create Database Connection geoprocessing tool in ArcGIS Pro or use Python to run the Create Database Connection command from an ArcGIS Server machine to create a database connection file (.sde) that connects to the database.
Note:
You cannot use the Create Database Connection geoprocessing tool if the connection will use Microsoft Azure Active Directory authentication.
Using the database connection file with ArcGIS Server requires the following:
- You must save database-authenticated, password-based Microsoft Azure Active Directory user information with the connection file.
Note:
You cannot register a database connection that uses Azure Active Directory - Universal with MFA with an ArcGIS Server site.
- If you use operating system authentication to connect, ensure that SQL Server can authenticate the ArcGIS Server account.
Tip:
Ensure the account used for the connection has the appropriate privileges on the data that the account needs to access. If the connection uses operating system authentication and you will publish web services using this connection, ensure that the ArcGIS Server account has the appropriate privileges on the data included in the web service. See Privileges for geodatabases in SQL Server and Privileges for using ArcGIS with a SQL Server database for more information.
Follow the steps below to connect to a SQL Server database from the Database Connection dialog box in ArcGIS Pro.
- Open the Catalog pane in ArcGIS Pro.
- Right-click Databases and click New Database Connection.
- Choose SQL Server from the Database Platform drop-down list.
- Type the SQL Server instance name in the Instance text box.
Examples include the following:
- For a SQL Server named instance of terra\gis, type terra\gis in the Instance text box.
- If you use a default SQL Server instance, you can specify the instance name or the IP address of the server in the Instance text box. If specifying an IPV6 address, enclose the address in brackets. For example, if the IPV6 address of the server is 2000:ab1:0:2:f333:c432:55f6:d7zz, type [2000:ab1:0:2:f333:c432:55f6:d7zz] in the Instance text box.
- If the SQL Server database is listening on a port other than the default (1433), you can include the port number in the instance. For example, if the SQL Server instance is named basset\spatial and is listening on port 61000, type basset\spatial,61000 in the Instance text box. Alternatively, you can provide the port number using the Port property. (See step 8 below.)
- For an Amazon RDS for SQL Server instance, the instance will be in the format <database_instance_identifier>.<region_id>.rds.amazonaws.com.
- For a Google Cloud SQL for SQL Server instance, type the public IP address of the instance in the Instance text box.
- For a Microsoft Azure SQL Managed Instance or Microsoft Azure SQL Database instance, provide the server name in the Instance text box. For example, if the server name is cloudy4u.dbid123.database.windows.net, type cloudy4u.dbid123.database.windows.net in the Instance text box.
If your SQL Server instance uses failover clusters, you can add conditions to the instance string to take advantage of the redundant instance.
You can append additional conditions to the instance name to request encrypted connections to the SQL Server instance.
- Choose the type of authentication to use when connecting to the database.
The Azure Active Directory options are supported only when connecting to Azure SQL Database or Azure SQL Managed Instance.
- Operating system authentication—You do not need to type a username and password; the connection is made using the login name and password used to sign in to the operating system. If the login used for the operating system is not a valid database login, the connection fails.
Database authentication—You must provide a valid database username and password in the User name and Password text boxes, respectively. Usernames can be a maximum of 30 characters.
- Azure Active Directory - Universal with MFA—You must provide the Azure Active Directory username but not the password. When you connect, a code is sent to you in a text message, email, or MFA device, or it can use a
fingerprint scan for authentication. This second part of the authentication process varies depending on how your network and authentication protocols are configured.
You cannot register a database connection that uses Azure Active Directory - Universal with MFA with an ArcGIS Server site.
- Azure Active Directory - Password—You must provide a valid Azure Active Directory username and password in the User name and Password text boxes, respectively. Usernames can be a maximum of 30 characters.
Note:
- Save user name and password must be checked for connection files that use database authentication or Azure Active Directory authentication to provide ArcGIS web services with access to the database, or if you need to search ArcGIS Pro to locate data accessed through this connection file.
- Outside of ArcGIS, you can create usernames in SQL Server that contain special characters. These usernames must be delimited whenever they are used. ArcGIS will add the delimiter automatically when it is passed to SQL Server; you do not need to include delimiters with the username. For example, if your username is map.user, type map.user, not "map.user", in the User name text box. For more information on regular and delimited identifiers, see the SQL Server documentation.
- In the Database text box, choose the name of the database you want to connect to on the SQL Server instance or Azure SQL Managed Instance, or, for all other database services, type the name of the database.
The database name is limited to 31 characters.
- Type a name for the connection file in the Connection File Name field.
As you provide connection information, a default name is created. You can use the default, or type a more descriptive file name.
This is the name that will appear in the Catalog pane and view, and the name of the .sde file stored on disk.
- Optionally, expand the Additional Properties section and specify parameters to connect that are in addition to those required in the previous steps.
- Port—If the SQL Server instance communicates over a nondefault port, choose this parameter from the Property drop-down menu and type the correct port in the Value field.
- Encrypt—Enables SSL encryption on the connection. Supported values are Yes or No.
- TrustServerCertificate—Allows encryption without need for a client certificate. Supported values are Yes or No.
- ApplicationIntent—Supports read-only routing if the user is connecting to the AlwaysOn availability group listener and read-only routing has been configured. Supported values are READONLY or READWRITE.
- MultisubnetFailover—Enables faster failover for all Availability Groups and failover cluster instance in SQL Server and will significantly reduce failover time for single and multi-subnet Always On topologies. Supported values are Yes or No.
- Click Validate to confirm that the connection information is valid and the database is accessible.
If the database contains a geodatabase and the connection is valid, the Geodatabase Properties tab is now active. If necessary, you can alter geodatabase connection properties to connect to a traditional version other than the default version, configure the connection as a branch version connection, or connect to a historical moment.
- Click OK to create the connection file.
A database connection appears under Databases in the Catalog pane, and a connection file (.sde) is created in the ArcGIS Pro project directory.
Register the database with ArcGIS Server
To allow ArcGIS Server sites to access the data, use the database connection file you created to add a registered data store in ArcGIS Pro or add a data store item in the portal.