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. Be sure to get 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 or Azure Active Directory -
Universal with Multi-Factor Authentication (MFA) username with the connection file.
Note:
If you use Azure Active Directory - Universal with MFA, the username saved in the database connection file and the network login used for the ArcGIS Server account must be in the same network domain.
- 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 these steps 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.
For example, if you are using 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:d7ee, type [2000:ab1:0:2:f333:c432:55f6:d7ee] in the Data source text box.
If the database is listening on a port other than the default (1433), include the port number in the instance. For example, if the SQL Server instance is basset\spatial and is listening on port 61000, type basset\spatial,61000 in the Instance text box.
If the Microsoft Azure SQL Database or Azure SQL Managed Instance instance name is cloudy4u.database.windows.net, type cloudy4u.database.windows.net in the Instance text box. If you are using a SQL Server instance named terra\gis, type terra\gis in the Instance text box. If your Amazon Relational Database Service (RDS) for SQL Server instance name is rdssqlserver.abc123 and it is deployed in Amazon Web Services region us-west-2, type rdssqlserver.abc123.us-west-2.rds.amazonaws.com in the Instance text box.
Additional text is needed in the Instance text box for encrypted connections and connections to highly available SQL Server databases.
- 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 Directoryusername 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.
- 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 specific 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.
- 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.
You can rename the file by typing a new name in the Catalog pane and pressing Enter.
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.