--- schema_version: "1" id: acfa05271c6e34bc6e9533f86fc805d05685ae16ffec8acdff8b70e2cd51246e title: Microsoft Azure SQL Database Connection collection: admin version: current locale: en-us portal_url: https://docs.salesforce.com/admin/current/en-us/analytics/264-0-0/bi_integrate_connectors_microsoft_azure_sql_db_settings.md canonical_url: https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_connectors_microsoft_azure_sql_db_settings.htm&release=264.0.0&type=5 source_site: help.salesforce.com content_type: documentation content_hash: 622855583ea2248f9068791c2163be9f6df7d4e8ab730b08aa0450d48d874467 description: Create a remote connection using the Microsoft Azure SQL Database connector to sync data from Microsoft Azure SQL Database to CRM Analytics. To connect to Microsoft Azure Synapse Analytics or a dedicated SQL pool (formerly SQL DW), add a Microsoft Azure Synapse Analytics connection instead of this one. products: - CRM Analytics - Cross Cloud release: "264" --- # Microsoft Azure SQL Database Connection Create a remote connection using the Microsoft Azure SQL Database connector to sync data from Microsoft Azure SQL Database to CRM Analytics. To connect to Microsoft Azure Synapse Analytics or a dedicated SQL pool (formerly SQL DW), add a Microsoft Azure Synapse Analytics connection instead of this one. ![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/images/icon_note.png) Note Azure SQL connections support both OAuth and SQL authentication with username and password. If you're not using OAuth with Entra-only authentication configured in Azure, authentication for this connection fails. In the Azure settings, disable the **Support only Microsoft Entra authentication for this server** setting. For more information, see [Microsoft Azure help](https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication-tutorial?view=azuresql&tabs=azure-portal). ## Connect to Azure SQL with OAuth To use the CRM Analytics Microsoft Azure SQL connector with an OAuth connection, you must configure Microsoft Azure and the CRM Analytics connector settings. These high-level instructions help you navigate the steps involved. Remember to contact your Network Security or IT department for help with configuration consistent with your organization’s security requirements. 1. Configure a connected app on your Microsoft Azure portal. For more information, see [Creating an Entra ID (Azure AD) Application](https://cdn.cdata.com/help/RUM/jdbc/pg_azureadoauthcustomappcreate.htm) During this configuration process, make note of the **Application (client) ID** and the **Directory (tenant) ID** values. These are the **Client Id** and **Azure Tenant** values in the connection settings. Get the **Client Secret** connector value from **New client secret** on the Client secrets tab. Use the **Value** and not the secret ID. The client secret value only shows once, so if you don't record it, you have to create another client secret to get the value. 2. Configure the Azure SQL Server to use Micrsoft Entra-only authentication or Use both SQL and Microsoft Entra authentication. Make sure the Microsoft Entra admin is configured. The server needs a firewall rule, which is configured in the Networks section under Security. For the rule, set the **Start IP** value to 0.0.0.0 and the **End IP** value to 255.255.255.255 and save. 3. Configure the Azure SQL Database, making sure to select the SQL database that matches the SQL server you configured for Microsoft Entra authentication. In the Query Editor, authenticate using your Microsoft Entra admin credentials and run these SQL statements, replacing `[my-sql-connector]` with the display name of your connected app. - `CREATE USER [my-sql-connector] FROM EXTERNAL PROVIDER;` - `ALTER ROLE db_datareader ADD MEMBER [my-sql-connector];` - `ALTER ROLE db_datawriter ADD MEMBER [my-sql-connector];` To confirm all of the configuaration steps are correctly implemented, run `SELECT name, type_desc FROM sys.database_principals WHERE name = "[my-sql-connector]";` and make sure the result contains one row with your connected app name. ## Create the Connection 1. On the Data Manager Connections tab, click **New Connection**. 2. Click the name of the connector, and click **Next**. 3. Enter the connector settings. 4. To validate your settings and attempt to connect to the source, click **Save & Test**. If the connection fails, CRM Analytics shows possible reasons. All settings require a value, unless otherwise indicated.
Connection Setting Description
Connection Name Required. Identifies the connection. Use a convention that lets you easily distinguish between different connections.
Developer Name Required. The API name for the connection. This name can’t include spaces. The API name is used in your recipes to reference data extracted through this connection. You can’t change the developer name after you create the connection.
Description Required. A description for the connection.
Authentication Type Required. The authentication type. Valid values include OAuth, for Entra-only authentication, and Password for standard authentication.
JDBC Connection URL

Required. The URL schema for the database. Enter in the format: <host name>:<port>

To form the host name, append .database.windows.net to the server name. The server name is found by following Microsoft’s documentation. For example, if your server’s name is Blue_server, the host name is Blue_server.database.windows.net. The default port is 1433.

URL example: 

Blue_server.database.windows.net:1433
Database Required. The Microsoft Azure SQL database name.
Schema

Optional. The schema name for the database. You must enter a schema name if the JDBC connection URL doesn’t provide enough context.

The default value is dbo

Username

Required for password authentication. The user name for the SQL database login.

Enter in the format: <username>@<host name>

To form the host name, append .database.windows.net to the server name. The server name is found by following Microsoft’s documentation. For example, if your server’s name is Blue_server, the host name is Blue_server.database.windows.net.

Password Required for password authentication. The password for the SQL database login.
Client ID Required for OAuth authentication. The Microsoft Azure application ID for the connected app.
Client Secret Required for OAuth authentication. The Microsoft Azure client secret value for the connected app.
Azure Tenant Required for OAuth authentication. The Microsoft Azure directory ID for the connected app.
## Filter Data Synced to CRM Analytics Exclude unnecessary or sensitive data from syncing to CRM Analytics with data sync filters. Filters run on the source object and speed up data sync by pulling only the data you need into CRM Analytics. If you plan to use excluded data in the future, use a recipe filter to limit the data written to a dataset instead of a data sync filter. 1. From Data Manager, click the **Connections** tab. 2. Select the connection associated with the object to filter. 3. Click the name of the object to filter. 4. Click **Data Sync Filter**. 5. Enter the filter. 6. Click **Save**. For the Microsoft Azure SQL Database connector,enter a filter in the syntax described in the [Azure SQL Database](https://docs.microsoft.com/en-us/azure/sql-database/) and [WHERE](https://docs.microsoft.com/en-us/sql/t-sql/queries/where-transact-sql?view=sql-server-ver15) documentation. Keep these behaviors in mind when working with the Microsoft Azure SQL Database connector. - Connected object names must start with a letter and contain only letters, digits, or underscores. Object names can’t end with an underscore. - Only field names with combinations of alphanumeric, dot, underscore, or dash characters are supported. If a connector includes field names that contain other characters, such as spaces or brackets, the sync fails. - The connector can sync up to 100 million rows or 50 GB per object, whichever limit is reached first. #### See Also - [Microsoft Azure SQL Database Connector Known Issues and Knowledge Articles](https://sfdc.co/msasqldbhconnsearch) - [Add and Remove Remote Objects and Fields from Data Sync](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_connectors_replicate_connection.htm&release=264.0.0&type=5 "Update data sync for connected external data sources to keep up with your changing business by adding objects, removing objects, and managing which fields are synced. Synced data is stored in CRM Analytics as objects that can be used in recipes.")