# Microsoft SQL Server security Security The Microsoft SQL Server database engine uses role-based security. The master user name that you specify when you create a DB instance is a SQL Server Authentication login that is a member of the `processadmin`, `public`, and `setupadmin` fixed server roles. Any user who creates a database is assigned to the db\$1owner role for that database and has all database-level permissions except for those that are used for backups. Amazon RDS manages backups for you. The following server-level roles aren't available in Amazon RDS for SQL Server: + bulkadmin + dbcreator + diskadmin + securityadmin + serveradmin + sysadmin The following server-level permissions aren't available on RDS for SQL Server DB instances: + ALTER ANY DATABASE + ALTER ANY EVENT NOTIFICATION + ALTER RESOURCES + ALTER SETTINGS (you can use the DB parameter group API operations to modify parameters; for more information, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md)) + AUTHENTICATE SERVER + CONTROL\$1SERVER + CREATE DDL EVENT NOTIFICATION + CREATE ENDPOINT + CREATE SERVER ROLE + CREATE TRACE EVENT NOTIFICATION + DROP ANY DATABASE + EXTERNAL ACCESS ASSEMBLY + SHUTDOWN (You can use the RDS reboot option instead) + UNSAFE ASSEMBLY + ALTER ANY AVAILABILITY GROUP + CREATE ANY AVAILABILITY GROUP ## SSL support for Microsoft SQL Server DB instances SSL support You can use SSL to encrypt connections between your applications and your Amazon RDS DB instances running Microsoft SQL Server. You can also force all connections to your DB instance to use SSL. If you force connections to use SSL, it happens transparently to the client, and the client doesn't have to do any work to use SSL. SSL is supported in all AWS Regions and for all supported SQL Server editions. For more information, see [Using SSL with a Microsoft SQL Server DB instance](SQLServer.Concepts.General.SSL.Using.md). # Using SSL with a Microsoft SQL Server DB instance Using SSL with a SQL Server DB instance You can use Secure Sockets Layer (SSL) to encrypt connections between your client applications and your Amazon RDS DB instances running Microsoft SQL Server. SSL support is available in all AWS regions for all supported SQL Server editions. When you create a SQL Server DB instance, Amazon RDS creates an SSL certificate for it. The SSL certificate includes the DB instance endpoint as the Common Name (CN) for the SSL certificate to guard against spoofing attacks. There are 2 ways to use SSL to connect to your SQL Server DB instance: + Force SSL for all connections — this happens transparently to the client, and the client doesn't have to do any work to use SSL. **Note** When you set `rds.force_ssl` to `1` and use SSMS version 19.3, 20.0, and 20.2, check for the following: Enable **Trust Server Certificate** in SSMS. Import the certificate in your system. + Encrypt specific connections — this sets up an SSL connection from a specific client computer, and you must do work on the client to encrypt connections. For information about Transport Layer Security (TLS) support for SQL Server, see [ TLS 1.2 support for Microsoft SQL Server](https://support.microsoft.com/en-ca/help/3135244/tls-1-2-support-for-microsoft-sql-server). ## Forcing connections to your DB instance to use SSL Forcing SSL You can force all connections to your DB instance to use SSL. If you force connections to use SSL, it happens transparently to the client, and the client doesn't have to do any work to use SSL. If you want to force SSL, use the `rds.force_ssl` parameter. By default, the `rds.force_ssl` parameter is set to `0 (off)`. Set the `rds.force_ssl` parameter to `1 (on)` to force connections to use SSL. The `rds.force_ssl` parameter is static, so after you change the value, you must reboot your DB instance for the change to take effect. **To force all connections to your DB instance to use SSL** 1. Determine the parameter group that is attached to your DB instance: 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the top right corner of the Amazon RDS console, choose the AWS Region of your DB instance. 1. In the navigation pane, choose **Databases**, and then choose the name of your DB instance to show its details. 1. Choose the **Configuration** tab. Find the **Parameter group** in the section. 1. If necessary, create a new parameter group. If your DB instance uses the default parameter group, you must create a new parameter group. If your DB instance uses a nondefault parameter group, you can choose to edit the existing parameter group or to create a new parameter group. If you edit an existing parameter group, the change affects all DB instances that use that parameter group. To create a new parameter group, follow the instructions in [Creating a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Creating.md). 1. Edit your new or existing parameter group to set the `rds.force_ssl` parameter to `true`. To edit the parameter group, follow the instructions in [Modifying parameters in a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md). 1. If you created a new parameter group, modify your DB instance to attach the new parameter group. Modify the **DB Parameter Group** setting of the DB instance. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). 1. Reboot your DB instance. For more information, see [Rebooting a DB instance](USER_RebootInstance.md). ## Encrypting specific connections Encrypting specific connections You can force all connections to your DB instance to use SSL, or you can encrypt connections from specific client computers only. To use SSL from a specific client, you must obtain certificates for the client computer, import certificates on the client computer, and then encrypt the connections from the client computer. **Note** All SQL Server instances created after August 5, 2014, use the DB instance endpoint in the Common Name (CN) field of the SSL certificate. Prior to August 5, 2014, SSL certificate verification was not available for VPC-based SQL Server instances. If you have a VPC-based SQL Server DB instance that was created before August 5, 2014, and you want to use SSL certificate verification and ensure that the instance endpoint is included as the CN for the SSL certificate for that DB instance, then rename the instance. When you rename a DB instance, a new certificate is deployed and the instance is rebooted to enable the new certificate. ### Obtaining certificates for client computers Obtaining certificates for client computers To encrypt connections from a client computer to an Amazon RDS DB instance running Microsoft SQL Server, you need a certificate on your client computer. To obtain that certificate, download the certificate to your client computer. You can download a root certificate that works for all regions. You can also download a certificate bundle that contains both the old and new root certificate. In addition, you can download region-specific intermediate certificates. For more information about downloading certificates, see [Using SSL/TLS to encrypt a connection to a DB instance or cluster ](UsingWithRDS.SSL.md). After you have downloaded the appropriate certificate, import the certificate into your Microsoft Windows operating system by following the procedure in the section following. ### Importing certificates on client computers Importing certificates on client computers You can use the following procedure to import your certificate into the Microsoft Windows operating system on your client computer. **To import the certificate into your Windows operating system:** 1. On the **Start** menu, type **Run** in the search box and press **Enter**. 1. In the **Open** box, type **MMC** and then choose **OK**. 1. In the MMC console, on the **File** menu, choose **Add/Remove Snap-in**. 1. In the **Add or Remove Snap-ins** dialog box, for **Available snap-ins**, select **Certificates**, and then choose **Add**. 1. In the **Certificates snap-in** dialog box, choose **Computer account**, and then choose **Next**. 1. In the **Select computer** dialog box, choose **Finish**. 1. In the **Add or Remove Snap-ins** dialog box, choose **OK**. 1. In the MMC console, expand **Certificates**, open the context (right-click) menu for **Trusted Root Certification Authorities**, choose **All Tasks**, and then choose **Import**. 1. On the first page of the Certificate Import Wizard, choose **Next**. 1. On the second page of the Certificate Import Wizard, choose **Browse**. In the browse window, change the file type to **All files (\$1.\$1)** because .pem is not a standard certificate extension. Locate the .pem file that you downloaded previously. **Note** When connecting from Windows clients such as SQL Server Management Studio (SSMS), we recommend using the PKCS\$17 (.p7b) certificate format instead of the global-bundle.pem file. The .p7b format ensures the complete certificate chain — including Root and Intermediate Certificate Authorities (CAs) — is correctly imported into the Windows Certificate Store. This prevents connection failures that can occur when mandatory encryption is enabled, as .pem imports may not install the full chain properly. 1. Choose **Open** to select the certificate file, and then choose **Next**. 1. On the third page of the Certificate Import Wizard, choose **Next**. 1. On the fourth page of the Certificate Import Wizard, choose **Finish**. A dialog box appears indicating that the import was successful. 1. In the MMC console, expand **Certificates**, expand **Trusted Root Certification Authorities**, and then choose **Certificates**. Locate the certificate to confirm it exists, as shown here. ![\[In the MMC console, in the navigation pane, the Certificates folder is selected drilled down from Console Root, Certificates (Local Computer), and Trusted Root Certification Authority. In the main page, select the required CA certificate.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/rds_sql_ssl_cert.png) ### Encrypting connections to an Amazon RDS DB instance running Microsoft SQL Server Encrypting connections After you have imported a certificate into your client computer, you can encrypt connections from the client computer to an Amazon RDS DB instance running Microsoft SQL Server. For SQL Server Management Studio, use the following procedure. For more information about SQL Server Management Studio, see [Use SQL Server management studio](http://msdn.microsoft.com/en-us/library/ms174173.aspx). **To encrypt connections from SQL Server Management Studio** 1. Launch SQL Server Management Studio. 1. For **Connect to server**, type the server information, login user name, and password. 1. Choose **Options**. 1. Select **Encrypt connection**. 1. Choose **Connect**. 1. Confirm that your connection is encrypted by running the following query. Verify that the query returns `true` for `encrypt_option`. ``` select ENCRYPT_OPTION from SYS.DM_EXEC_CONNECTIONS where SESSION_ID = @@SPID ``` For any other SQL client, use the following procedure. **To encrypt connections from other SQL clients** 1. Append `encrypt=true` to your connection string. This string might be available as an option, or as a property on the connection page in GUI tools. **Note** To enable SSL encryption for clients that connect using JDBC, you might need to add the Amazon RDS SQL certificate to the Java CA certificate (cacerts) store. You can do this by using the [ keytool](http://docs.oracle.com/javase/7/docs/technotes/tools/solaris/keytool.html) utility. 1. Confirm that your connection is encrypted by running the following query. Verify that the query returns `true` for `encrypt_option`. ``` select ENCRYPT_OPTION from SYS.DM_EXEC_CONNECTIONS where SESSION_ID = @@SPID ``` # Configuring SQL Server security protocols and ciphers You can turn certain security protocols and ciphers on and off using DB parameters. The security parameters that you can configure (except for TLS version 1.2) are shown in the following table. **** | DB parameter | Allowed values (default in bold) | Description | | --- | --- | --- | | rds.tls10 | default, enabled, disabled | TLS 1.0. | | rds.tls11 | default, enabled, disabled | TLS 1.1. | | rds.tls12 | default | TLS 1.2. You can't modify this value. | | rds.fips | 0, 1 | When you set the parameter to 1, RDS forces the use of modules that are compliant with the Federal Information Processing Standard (FIPS) 140-2 standard. For more information, see [Use SQL Server 2016 in FIPS 140-2-compliant mode](https://docs.microsoft.com/en-us/troubleshoot/sql/security/sql-2016-fips-140-2-compliant-mode) in the Microsoft documentation. | | rds.rc4 | default, enabled, disabled | RC4 stream cipher. | | rds.diffie-hellman | default, enabled, disabled | Diffie-Hellman key-exchange encryption. | | rds.diffie-hellman-min-key-bit-length | default, 1024, 2048, 3072, 4096 | Minimum bit length for Diffie-Hellman keys. | | rds.curve25519 | default, enabled, disabled | Curve25519 elliptic-curve encryption cipher. This parameter isn't supported for all engine versions. | | rds.3des168 | default, enabled, disabled | Triple Data Encryption Standard (DES) encryption cipher with a 168-bit key length. | **Note** For minor engine versions after 16.00.4120.1, 15.00.4365.2, 14.00.3465.1, 13.00.6435.1, and 12.00.6449.1, the default setting for the DB parameters `rds.tls10`, `rds.tls11`, `rds.rc4`, `rds.curve25519`, and `rds.3des168` is *disabled*. Otherwise the default setting is *enabled*. For minor engine versions after 16.00.4120.1, 15.00.4365.2, 14.00.3465.1, 13.00.6435.1, and 12.00.6449.1, the default setting for `rds.diffie-hellman-min-key-bit-length`is 3072. Otherwise the default setting is 2048. Use the following process to configure the security protocols and ciphers: 1. Create a custom DB parameter group. 1. Modify the parameters in the parameter group. 1. Associate the DB parameter group with your DB instance. For more information on DB parameter groups, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md). ## Creating the security-related parameter group Create a parameter group for your security-related parameters that corresponds to the SQL Server edition and version of your DB instance. ### Console The following procedure creates a parameter group for SQL Server Standard Edition 2016. **To create the parameter group** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, choose **Parameter groups**. 1. Choose **Create parameter group**. 1. In the **Create parameter group** pane, do the following: 1. For **Parameter group family**, choose **sqlserver-se-13.0**. 1. For **Group name**, enter an identifier for the parameter group, such as **sqlserver-ciphers-se-13**. 1. For **Description**, enter **Parameter group for security protocols and ciphers**. 1. Choose **Create**. ### CLI The following procedure creates a parameter group for SQL Server Standard Edition 2016. **To create the parameter group** + Run one of the following commands. **Example** For Linux, macOS, or Unix: ``` aws rds create-db-parameter-group \ --db-parameter-group-name sqlserver-ciphers-se-13 \ --db-parameter-group-family "sqlserver-se-13.0" \ --description "Parameter group for security protocols and ciphers" ``` For Windows: ``` aws rds create-db-parameter-group ^ --db-parameter-group-name sqlserver-ciphers-se-13 ^ --db-parameter-group-family "sqlserver-se-13.0" ^ --description "Parameter group for security protocols and ciphers" ``` ## Modifying security-related parameters Modify the security-related parameters in the parameter group that corresponds to the SQL Server edition and version of your DB instance. ### Console The following procedure modifies the parameter group that you created for SQL Server Standard Edition 2016. This example turns off TLS version 1.0. **To modify the parameter group** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, choose **Parameter groups**. 1. Choose the parameter group, such as **sqlserver-ciphers-se-13**. 1. Under **Parameters**, filter the parameter list for **rds**. 1. Choose **Edit parameters**. 1. Choose **rds.tls10**. 1. For **Values**, choose **disabled**. 1. Choose **Save changes**. ### CLI The following procedure modifies the parameter group that you created for SQL Server Standard Edition 2016. This example turns off TLS version 1.0. **To modify the parameter group** + Run one of the following commands. **Example** For Linux, macOS, or Unix: ``` aws rds modify-db-parameter-group \ --db-parameter-group-name sqlserver-ciphers-se-13 \ --parameters "ParameterName='rds.tls10',ParameterValue='disabled',ApplyMethod=pending-reboot" ``` For Windows: ``` aws rds modify-db-parameter-group ^ --db-parameter-group-name sqlserver-ciphers-se-13 ^ --parameters "ParameterName='rds.tls10',ParameterValue='disabled',ApplyMethod=pending-reboot" ``` ## Associating the security-related parameter group with your DB instance To associate the parameter group with your DB instance, use the AWS Management Console or the AWS CLI. ### Console You can associate the parameter group with a new or existing DB instance: + For a new DB instance, associate it when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + For an existing DB instance, associate it by modifying the instance. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). ### CLI You can associate the parameter group with a new or existing DB instance. **To create a DB instance with the parameter group** + Specify the same DB engine type and major version as you used when creating the parameter group. **Example** For Linux, macOS, or Unix: ``` aws rds create-db-instance \ --db-instance-identifier mydbinstance \ --db-instance-class db.m5.2xlarge \ --engine sqlserver-se \ --engine-version 13.00.5426.0.v1 \ --allocated-storage 100 \ --master-user-password secret123 \ --master-username admin \ --storage-type gp2 \ --license-model li \ --db-parameter-group-name sqlserver-ciphers-se-13 ``` For Windows: ``` aws rds create-db-instance ^ --db-instance-identifier mydbinstance ^ --db-instance-class db.m5.2xlarge ^ --engine sqlserver-se ^ --engine-version 13.00.5426.0.v1 ^ --allocated-storage 100 ^ --master-user-password secret123 ^ --master-username admin ^ --storage-type gp2 ^ --license-model li ^ --db-parameter-group-name sqlserver-ciphers-se-13 ``` **Note** Specify a password other than the prompt shown here as a security best practice. **To modify a DB instance and associate the parameter group** + Run one of the following commands. **Example** For Linux, macOS, or Unix: ``` aws rds modify-db-instance \ --db-instance-identifier mydbinstance \ --db-parameter-group-name sqlserver-ciphers-se-13 \ --apply-immediately ``` For Windows: ``` aws rds modify-db-instance ^ --db-instance-identifier mydbinstance ^ --db-parameter-group-name sqlserver-ciphers-se-13 ^ --apply-immediately ``` # Updating applications to connect to Microsoft SQL Server DB instances using new SSL/TLS certificates Updating applications for new SSL/TLS certificates As of January 13, 2023, Amazon RDS has published new Certificate Authority (CA) certificates for connecting to your RDS DB instances using Secure Socket Layer or Transport Layer Security (SSL/TLS). Following, you can find information about updating your applications to use the new certificates. This topic can help you to determine whether any client applications use SSL/TLS to connect to your DB instances. If they do, you can further check whether those applications require certificate verification to connect. **Note** Some applications are configured to connect to SQL Server DB instances only if they can successfully verify the certificate on the server. For such applications, you must update your client application trust stores to include the new CA certificates. After you update your CA certificates in the client application trust stores, you can rotate the certificates on your DB instances. We strongly recommend testing these procedures in a development or staging environment before implementing them in your production environments. For more information about certificate rotation, see [Rotating your SSL/TLS certificate](UsingWithRDS.SSL-certificate-rotation.md). For more information about downloading certificates, see [Using SSL/TLS to encrypt a connection to a DB instance or cluster ](UsingWithRDS.SSL.md). For information about using SSL/TLS with Microsoft SQL Server DB instances, see [Using SSL with a Microsoft SQL Server DB instance](SQLServer.Concepts.General.SSL.Using.md). **Topics** + [ ## Determining whether any applications are connecting to your Microsoft SQL Server DB instance using SSL ](#ssl-certificate-rotation-sqlserver.determining-server) + [ ## Determining whether a client requires certificate verification in order to connect ](#ssl-certificate-rotation-sqlserver.determining-client) + [ ## Updating your application trust store ](#ssl-certificate-rotation-sqlserver.updating-trust-store) ## Determining whether any applications are connecting to your Microsoft SQL Server DB instance using SSL Check the DB instance configuration for the value of the `rds.force_ssl` parameter. By default, the `rds.force_ssl` parameter is set to 0 (off). If the `rds.force_ssl` parameter is set to 1 (on), clients are required to use SSL/TLS for connections. For more information about parameter groups, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md). Run the following query to get the current encryption option for all the open connections to a DB instance. The column `ENCRYPT_OPTION` returns `TRUE` if the connection is encrypted. ``` select SESSION_ID, ENCRYPT_OPTION, NET_TRANSPORT, AUTH_SCHEME from SYS.DM_EXEC_CONNECTIONS ``` This query shows only the current connections. It doesn't show whether applications that have connected and disconnected in the past have used SSL. ## Determining whether a client requires certificate verification in order to connect You can check whether different types of clients require certificate verification to connect. **Note** If you use connectors other than the ones listed, see the specific connector's documentation for information about how it enforces encrypted connections. For more information, see [Connection modules for Microsoft SQL databases](https://docs.microsoft.com/en-us/sql/connect/sql-connection-libraries?view=sql-server-ver15) in the Microsoft SQL Server documentation. ### SQL Server Management Studio Check whether encryption is enforced for SQL Server Management Studio connections: 1. Launch SQL Server Management Studio. 1. For **Connect to server**, enter the server information, login user name, and password. 1. Choose **Options**. 1. Check if **Encrypt connection** is selected in the connect page. For more information about SQL Server Management Studio, see [Use SQL Server Management Studio](http://msdn.microsoft.com/en-us/library/ms174173.aspx). ### Sqlcmd The following example with the `sqlcmd` client shows how to check a script's SQL Server connection to determine whether successful connections require a valid certificate. For more information, see [Connecting with sqlcmd](https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/connecting-with-sqlcmd?view=sql-server-ver15) in the Microsoft SQL Server documentation. When using `sqlcmd`, an SSL connection requires verification against the server certificate if you use the `-N` command argument to encrypt connections, as in the following example. ``` $ sqlcmd -N -S dbinstance.rds.amazon.com -d ExampleDB ``` **Note** If `sqlcmd` is invoked with the `-C` option, it trusts the server certificate, even if that doesn't match the client-side trust store. ### ADO.NET In the following example, the application connects using SSL, and the server certificate must be verified. ``` using SQLC = Microsoft.Data.SqlClient; ... static public void Main() { using (var connection = new SQLC.SqlConnection( "Server=tcp:dbinstance.rds.amazon.com;" + "Database=ExampleDB;User ID=LOGIN_NAME;" + "Password=YOUR_PASSWORD;" + "Encrypt=True;TrustServerCertificate=False;" )) { connection.Open(); ... } ``` ### Java In the following example, the application connects using SSL, and the server certificate must be verified. ``` String connectionUrl = "jdbc:sqlserver://dbinstance.rds.amazon.com;" + "databaseName=ExampleDB;integratedSecurity=true;" + "encrypt=true;trustServerCertificate=false"; ``` To enable SSL encryption for clients that connect using JDBC, you might need to add the Amazon RDS certificate to the Java CA certificate store. For instructions, see [Configuring the client for encryption](https://docs.microsoft.com/en-us/SQL/connect/jdbc/configuring-the-client-for-ssl-encryption?view=sql-server-2017) in the Microsoft SQL Server documentation. You can also provide the trusted CA certificate file name directly by appending `trustStore=path-to-certificate-trust-store-file` to the connection string. **Note** If you use `TrustServerCertificate=true` (or its equivalent) in the connection string, the connection process skips the trust chain validation. In this case, the application connects even if the certificate can't be verified. Using `TrustServerCertificate=false` enforces certificate validation and is a best practice. ## Updating your application trust store You can update the trust store for applications that use Microsoft SQL Server. For instructions, see [Encrypting specific connections](SQLServer.Concepts.General.SSL.Using.md#SQLServer.Concepts.General.SSL.Client). Also, see [Configuring the client for encryption](https://docs.microsoft.com/en-us/SQL/connect/jdbc/configuring-the-client-for-ssl-encryption?view=sql-server-2017) in the Microsoft SQL Server documentation. If you are using an operating system other than Microsoft Windows, see the software distribution documentation for SSL/TLS implementation for information about adding a new root CA certificate. For example, OpenSSL and GnuTLS are popular options. Use the implementation method to add trust to the RDS root CA certificate. Microsoft provides instructions for configuring certificates on some systems. For information about downloading the root certificate, see [Using SSL/TLS to encrypt a connection to a DB instance or cluster ](UsingWithRDS.SSL.md). For sample scripts that import certificates, see [Sample script for importing certificates into your trust store](UsingWithRDS.SSL-certificate-rotation.md#UsingWithRDS.SSL-certificate-rotation-sample-script). **Note** When you update the trust store, you can retain older certificates in addition to adding the new certificates.