Microsoft SQL Server

Create a Microsoft SQL Server datastore to connect to a Microsoft SQL Server database.

Microsoft SQL Server database datastores support a number of specific configurable options. Configure the datastore to match your Microsoft SQL Server database.

Prerequisites when using Azure Active Directory authentication with passwords or managed identities:

You must have installed SQL Server ODBC Driver 18 (Microsoft Windows) or DataDirect ODBC Driver V8.0 SP2 (Linux) on the Agent machine.
You must have enabled TLS 1.2 or above on the Agent machine, which is enabled by default in several Microsoft Windows versions.
For Microsoft Managed Identities, you must have the Agent running on an Azure-hosted VM.

Caution

If you are using Azure PaaS with agents that are older than the 2309 release, be aware that running a job uses the authentication method SQL Server Authentication despite your being able to select Active Directory – Password in Authentication Method for database subtype Azure PaaS. Pre-2309 agents do not recognize the UI parameter Authentication Method. Since the user credentials are different, the job will fail with an error about incorrect credentials.

Option	Possible values	Description
Database Subtype	On Premise Azure PaaS Azure VM	Select the database subtype, which determines certain configuration options such as authentication method.
SQL Server version	Microsoft SQL Server `<version number>`	The version of your SQL Server client. This is the version of SQL Server that this datastore accesses.
Database server name	Computer name, fully qualified domain name, or IP address	The name of the host system where the SQL Server instance is located.
Database name	Refer to the requirements of your database	The name of the database to which the datastore connects.
User name	Alphanumeric characters and underscores	The user name of the account through which SAP Cloud Integration for data services accesses the database. For SQL Server authentication, enter your database credentials. For Active Directory - Password authentication, enter your Azure credentials. For Windows Authentication, the User name field does not appear.
Password	Alphanumeric characters, underscores, and punctuation	The user's password. For SQL Server authentication, enter your database credentials. For Active Directory - Password authentication, enter your Azure credentials. For Windows authentication, the Password field does not appear.
Authentication Method	Windows Authentication Active Directory - Password Active Directory - Managed Identity [Neo environment only] SQL Server Authentication	The type of authentication used to connect to this datastore. For an On Premise or Azure VM database subtype, select SQL Server Authentication or Windows Authentication. For an Azure PaaS database subtype, select Active Directory - Password, Active Directory - Managed Identity, or SQL Server Authentication. Be sure to enter the appropriate credentials in User name and Password. For Active Directory - Managed Identity, a user name and password are not needed, and the fields are hidden once Active Directory - Managed Identity has been selected as the database subtype. For more information about working with managed identities, see [Neo environment only] Setting Up Your Environment for Microsoft Managed Identities. Restriction Authentication using Microsoft managed identities is certified for on-premise Agents running on Microsoft Windows environments only and is not supported for Linux environments. For more information about how to use Windows authentication with Microsoft SQL Server, refer to Microsoft's SQL Server documentation.
Use SSL encryption	Yes No	SSL encryption protects data that is transferred between the database server and the Agent. The default is Yes. For the On Premise and Azure VM database subtypes, you can choose to enable or disable SSL encryption. For the Azure PaaS database subtype, SSL encryption is automatically enabled and you cannot deselect the option. On Microsoft Azure, Microsoft SQL Server uses the default self-signing mechanism for secure connectivity. No additional action is necessary.
Azure User Assigned Identity	Alphanumeric characters, underscores, and punctuation	The client ID of the Managed Identity user as created in the Microsoft Azure Access Control (IAM) application.

For information about how to set up a Microsoft SQL Server Connection on Linux using a DataDirect driver for SAP Cloud Integration for data services Agent, see Knowledge Base Article 3202261 Information published on SAP site .