SQL Server Containers and SQL Server on Linux Now Available on Windows via WSL!
We're kicking off 2025 with exciting news! We are thrilled to announce that developers can now easily get started with SQL Server container images and SQL Server on Linux installations directly on their Windows machines using Windows Subsystem for Linux (WSL). This new capability is designed to streamline your development process, making it more efficient and versatile. Why Use SQL Server on WSL? With WSL, you can run a Linux environment directly on Windows without the overhead of a traditional virtual machine. This means you can now develop and test your SQL Server applications in a Linux environment, right from your Windows desktop. Here are some key benefits: Seamless Integration: Easily switch between Windows and Linux environments. Development Focused: Ideal for development and testing purposes. Giving Developers access to the power of both Windows and Linux at the same time on a Windows machine. For detailed instructions on installation and getting started with WSL, please refer Install WSL | Microsoft Learn. Important Note: This setup is intended for development and testing purposes only and is not supported for production environments. There are two ways to get started with SQL Server on WSL: Install SQL Server as a systemd service: This allows you to manage it using systemctl commands. Deploy SQL Server containers in WSL: You can also deploy SQL Server containers, with the option for persistent storage if needed. For more information, please refer to the tutorial on WSL 2: Install SQL Server on Windows Subsystem for Linux - SQL Server on Microsoft Learn. Getting Started Install WSL: If you haven't already, install WSL on your Windows machine. You can follow the official Microsoft documentation for a step-by-step guide. Choose your Linux distribution: We recommend running SQL Server in WSL environments on one of the Supported platforms as documented, for the version of SQL Server you intend to run. Set Up SQL Server Containers: Pull the latest SQL Server container images from the Docker Hub and set them up in your WSL environment. (and/or) Install SQL Server on Linux: Alternatively, you can install SQL Server directly on your Linux distribution within WSL. Thanks, Andrew Carter (lead), Eric Julien from the Engineering team, Rafid Lafta from CSS and Randolph West for the Technical documentation.
Managed Identity support for Azure Key Vault in SQL Server running on Linux
We are happy to announce that, you can now use Managed Identity to authenticate to Azure Key Vault from SQL Server running on Azure VM (Linux) available from SQL Server 2022 CU18 onwards. This blog will walk you through the process of using a user-assigned managed identity to access Azure Key Vault and configure Transparent Data Encryption(TDE) for a SQL database. Managed Identity: Microsoft Entra ID, formerly Azure Active Directory, provides an automatically managed identity to authenticate to any Azure service that supports Microsoft Entra authentication, such as Azure Key Vault, without exposing credentials in the code. Refer Managed identities for Azure resources - Managed identities for Azure resources | Microsoft Learn for more details. VM Setup and Prerequisites: Before diving into the setup, it's essential to ensure that your Azure Linux VM has SQL Server installed and that the VM has identities assigned with the necessary key vault permissions. Set up SQL Server running on Azure Linux VM. Refer SQL Server on RHEL VM in Azure: RHEL: Install SQL Server on Linux - SQL Server | Microsoft Learn, SQL Server on SLES VM in Azure: SUSE: Install SQL Server on Linux - SQL Server | Microsoft Learn, SQL Server on Ubuntu VM in Azure: Ubuntu: Install SQL Server on Linux - SQL Server | Microsoft Learn for more details. Create user-assigned Managed Identity. Refer https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/how-to-manage-ua-identity-portal for more details. Go to Azure Linux VM resource in the Azure portal and click on Identity tab under security blade. Go to the User assigned tab in the right side panel and click on Add. Select the user-assigned managed identity and click on Add. Create a Key Vault and Keys. Refer Integrate Key Vault with SQL Server on Windows VMs in Azure (Resource Manager) - SQL Server on Azure VMs | Microsoft Learn for more details. Assign Key Vault Crypto Service Encryption User role to the user-assigned managed identity to perform wrap and unwrap operations. Go to the key vault resource that you created, and select the Access control (IAM)setting. Select Add> Add role assignment. Search for Key Vault Crypto Service Encryption User and select the role. Select Next. In the Members tab, select Managed identity option and click on Select members option, and then search for the user-assigned managed identity that you created in Step 3. Select the managed identity and then click on Select button. Setting the primary identity on Azure Linux VM To set the managed identity as the primary identity for Azure Linux VM, you can use the mssql-conf tool packaged with SQL Server. Here are the steps: Use the mssql-conf tool to manually set the primary identity. Run the following commands: sudo /opt/mssql/bin/mssql-conf set network.aadmsiclientid <client id of the managed identity> sudo /opt/mssql/bin/mssql-conf set network.aadprimarytenant <tenant id> 3. Restart the SQL Server: sudo systemctl restart mssql-server Enable TDE using EKM and managed identity: Refer Managed Identity Support for Extensible Key Management (EKM) with Azure Key Vault (AKV) - SQL Server on Azure VMs | Microsoft Learn for configuration steps for Azure Windows VM. These steps remain same for SQL Server running on an Azure Linux VM. 1.Enable EKM in SQL Server running on the Azure VM. 2.Create credential and encrypt the database. When using the CREATE CREDENTIAL command in this context, you only need to provide the 'Managed Identity' in the IDENTITY argument. Unlike earlier scenarios, you do not need to include a SECRET argument. This simplifies the process and enhances security by not requiring a secret to be passed. Conclusion: Using managed identity to access Azure Key Vault in SQL Server running on an Azure Linux VM boosts security, streamlines key management, and supports compliance. With data protection being paramount, Azure Key Vault’s integration along with managed identity offers a robust solution. Stay tuned for more insights on SQL Server on Linux! Official Documentation: Managed Identity Support for Extensible Key Management (EKM) with Azure Key Vault (AKV) - SQL Server on Azure VMs | Microsoft Learn Extensible Key Management using Azure Key Vault - SQL Server Setup Steps for Extensible Key Management Using the Azure Key Vault Azure Key Vault Integration for SQL Server on Azure VMsSQL Server 2022 for RHEL 9 and Ubuntu 22.04 is now Generally Available (GA)
We are happy to announce that Starting CU 10 release for SQL Server 2022 , SQL Server 2022 on RHEL 9 and Ubuntu 22.04 is now generally available (GA), meaning you can run production workload on SQL Server 2022 deployed on Red Hat Enterprise Linux (RHEL) 9 and Ubuntu 22.04. For SQL Server 2022 on RHEL 9, the SELinux integration that enables you to run SQL Server 2022 on RHEL 9 as confined application is also now generally available. If you've had the preview packages installed previously, we recommend that you please uninstall the preview bits and upgrade to SQL Server 2022 GA packages available at the following repos, for steps on how to update repos refer the Configure repositories for installing and upgrading SQL Server on Linux - SQL Server | Microsoft Learn documentation. For RHEL 9 use the repo: https://packages.microsoft.com/config/rhel/9/ For Ubuntu 22.04 use the repo: https://packages.microsoft.com/config/ubuntu/22.04/ For further details on getting started with the installation of SQL Server 2022 on RHEL 9 refer: RHEL: Install SQL Server on Linux - SQL Server | Microsoft Learn & for SQL Server on Ubuntu 22.04 refer: Ubuntu: Install SQL Server on Linux - SQL Server | Microsoft Learn SQL Server 2022 container images on RHEL 9 & Ubuntu 22.04 also generally available: SQL Server 2022 on RHEL 9 and Ubuntu 22.04 are also available as container images and generally available (GA). It is easy for you to get started. Like always, you can use both podman and/or docker tool. To pull and run the production ready SQL Server 2022 images on RHEL 9 use the tag: 2022-CU10-rhel-9.1 #Pull the image podman pull mcr.microsoft.com/mssql/rhel/server:2022-CU10-rhel-9.1 #Run a container using the above image podman run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourP@ssword>' -e 'MSSQL_PID=Developer' -h sql22rhel9 --name sql22rhel9 -p 1433:1433 -d mcr.microsoft.com/mssql/rhel/server:2022-CU10-rhel-9.1 To pull and run the production ready SQL Server 2022 images on Ubuntu 22.04 use the tag: 2022-CU10-ubuntu-22.04 #Pull the image docker pull mcr.microsoft.com/mssql/server:2022-CU10-ubuntu-22.04 #Run a container use the above image docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourP@ssword>' -e 'MSSQL_PID=Developer' -h sql22ubu2204 --name sql22ubu2204 -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-CU10-ubuntu-22.04SQL Server 2022 now available for both RHEL 9 and Ubuntu 22.04 in Preview mode
Update as of 23 Nov 2023: SQL Server 2022 on RHEL 9 and Ubuntu 22.04 is now GA starting with the release of CU 10 for SQL Server 2022. For details, please refer the GA blog here: SQL Server 2022 for RHEL 9 and Ubuntu 22.04 is now Generally Available (GA) - Microsoft Community Hub We are glad to announce that SQL Server 2022 is now available in preview mode for both Red Hat Enterprise Linux (RHEL) 9 and Ubuntu 22.04. For this preview, only Evaluation edition is available, which is limited to 180 days starting Thursday, July 27th, 2023. In your Dev/Test environments, you may now take advantage of the most recent SQL Server 2022 improvements on both RHEL 9 and Ubuntu 22.04. Currently, production workloads on RHEL 9 and Ubuntu 22.04 are not supported by the SQL Server 2022 preview packages. You can run the production workloads for SQL Server 2022 on RHEL 8 and Ubuntu 22.04 and they are fully supported. SELinux Integration: With this preview release, you now have the option to run SQL Server 2022 as a confined application when you have the SELinux enabled and in enforcing mode. Thus, seamlessly integrating with the SELinux enabled secure environments. This integration is made possible by the new package called "mssql-server-selinux" which enables the custom policies required to run SQL Server as confined application with SELinux. For more information about confined and unconfined applications with SELinux, see Getting started with SELinux. If you prefer to run SQL Server as unconfined application like in previous version of RHEL, then you can skip the installation of the "mssql-server-selinux" package and just install the mssql-server package like you did with the previous RHEL versions. For details on how you can get started with SQL Server 2022 on RHEL 9 preview please refer: RHEL: Install SQL Server on Linux - SQL Server | Microsoft Learn Getting Started with SQL Server 2022 on Ubuntu 22.04 (preview) You can get started with the SQL Server 2022 on Ubuntu 22.04 preview for details refer : Ubuntu: Install SQL Server on Linux - SQL Server | Microsoft Learn SQL Server Container images (preview) The preview packages for SQL Server 2022 on RHEL 9 and Ubuntu 22.04 are also available as container images and it is easy for you to get started. Like always, you can use both podman and/or docker tool. SQL Server 2022 preview container image based on RHEL 9: You can pull the preview container image using the tag: 2022-preview-rhel-9 as shown below podman pull mcr.microsoft.com/mssql/rhel/server:2022-preview-rhel-9 Deploy the SQL Server containers using the command below, please note the use of Evaluation edition as this is currently in preview podman run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourP@ssword>' -e 'MSSQL_PID=Evaluation' -h sqlpreview --name sqlpreview -p 1433:1433 -d mcr.microsoft.com/mssql/rhel/server:2022-preview-rhel-9 SQL Server 2022 preview container image based on Ubuntu 22.04: You can pull the preview container image using the tag: 2022-preview-ubuntu-22.04 as shown below docker pull mcr.microsoft.com/mssql/server:2022-preview-ubuntu-22.04 Deploy the SQL Server containers using the command below, please note the use of Evaluation edition as this is currently in preview docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourP@ssword>' -e 'MSSQL_PID=Evaluation' -h sqlpreview --name sqlpreview -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-preview-ubuntu-22.04 For further details on container images, refer: For RHEL based SQL Server 2022 preview container images: Microsoft SQL Server - RHEL based images For Ubuntu based SQL Server 2022 preview container images: Microsoft SQL Server - Ubuntu based images Let us know your experience with the preview package: If you have any feedback or issue that you'd like to report for these preview packages we recommend you to use any of the following options that suits you the best. 1) Send us an email with your feedback to sqlpreviewpackage@microsoft.com. 2) Another option would be to submit your comments directly on Azure Ideas ( Use the SQL Server on Linux Group on the left side of the page) 3) Alternatively, you can open issues related to the preview packages Issues · microsoft/mssql-docker (github.com) on GitHub. We hope you give SQL Server 2022 preview packages on Red Hat 9 and Ubuntu 22.04 a try and let us know what you think!SQL Server containers on Kubernetes with S3-compatible object storage - Getting started
One of the new features introduced with SQL Server 2022 is the ability to connect to any S3-compatible object storage and SQL Server supports both Backup/Restore and data lake virtualization with Polybase integration. In this blog, we will demonstrate both of these features for SQL Server 2022 Containers running on Kubernetes. As usual, I will use the Azure Kubernetes Service as my Kubernetes environment 🙂. To know more about these features, refer SQL Server backup to URL for S3-compatible object storage - SQL Server | Microsoft Learn and Access external data: S3-compatible object storage - PolyBase - SQL Server | Microsoft Learn. Before we enable Polybase for SQL Server containers, let's go over the prerequisites for this demo: Pre-requisites: You need to have an S3 – Object storage that you can connect to and in this blog, I have configured a MinIO based S3-object storage server on Ubuntu with TLS configured. For details on how to configure this refer: Deploy MinIO: Single-Node Single-Drive — MinIO Object Storage for Linux and Network Encryption (TLS) — MinIO Object Storage for Linux. MinIO setting details: Minio Console is configured at : https://10.0.0.82:9001/ and the Server endpoint is configured at : https://10.0.0.82:9000/ On the MinIO server, I have enabled ports 9000 and 9001 in the firewall. The public certificate that I used to configure TLS on the MinIO server is called "public.crt," and I have it downloaded and saved on my Windows client. Customized SQL Server Container: Let's create the customized SQL Server container image, that'll be used to deploy the SQL Server containers on the Kubernetes cluster. Here are the steps for creating the customised SQL Server containers; for this demonstration, I created the custom container image for this demo on the same Ubuntu-based Azure VM that I used to configure the MinIO Server. ## Step 1: Create a DockerFile with the below content # Base OS layer: Latest Ubuntu LTS FROM mcr.microsoft.com/mssql/server:2022-latest USER root # Install prerequistes since it is needed to get repo config for SQL server RUN apt-get update && \ apt-get install -y software-properties-common && \ rm -rf /var/lib/apt/lists/* RUN add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2022.list)" && \ apt-get install -y mssql-server-polybase EXPOSE 1433 USER mssql # Run SQL Server process CMD ["/opt/mssql/bin/sqlservr"] # Step 2: Let's build the container image sudo docker build -t mssql-pb-update . # Step 3: Tag the image and push to your container registry, in this case I am using Azure Container Registry(ACR)that I have. sudo docker tag sql-pb-update amvinacr.azurecr.io/sql-pb-update:latest sudo docker tag mssql-pb-update amvinacr.azurecr.io/sql-pb-update:latest # To know more about the ACR refer: https://learn.microsoft.com/en-us/azure/container-registry/container-registry-tutorial-quick-task Azure Kubernetes Service based cluster creation and attaching it to the Azure Container Registry(ACR) In this blog, I am deploying SQL Server containers on Azure Kubernetes Service(AKS) based Kubernetes cluster called "sqlk8s", I have integrated my AKS cluster with the above Azure Container Registry(ACR) to which I pushed my images in the above sample code. For detailed steps on AKS cluster creation and attaching it to your choice of container registry in Azure please refer the below two articles: Quickstart: Deploy an AKS cluster by using Azure CLI - Azure Kubernetes Service | Microsoft Learn Integrate Azure Container Registry with Azure Kubernetes Service - Azure Kubernetes Service | Microsoft Learn Let's get started With the pre-requisties done, now we can start deploying SQL Server containers on our AKS cluster using the custom image that we created, that installs polybase. But, before we start deploying SQL Server containers, we need two configmaps and one secret object in the AKS cluster, with the details as shown below, I am running all these commands from my Windows client which has kubectl installed and is able to connect to the AKS cluster 'sqlk8s' that I created in previous steps. As the endpoint will be validated by the certificate, we will use one configmap object to load the public.crt certificate required for configuring the secure channel between SQL Server and the MinIO Storage. You can use the command below to generate the configmap from the public.crt file. I have copied the certificate public.crt to the following location on my Windows machine: "C:\pb\demo\certs\public.crt" # Use the below command to create the configmap using the file "public.crt" kubectl create configmap mssql-tls --from-file="C:\pbdemo\certs\public.crt" # You can verify that the, the configmap is created using the command below and the sample output is shown below with obfuscated lines in the output. C:\>kubectl describe configmap mssql-tls Name: mssql-tls Namespace: default Labels: <none> Annotations: <none> Data ==== public.crt: ---- -----BEGIN CERTIFICATE----- MIIDjjCCAnagAwIBAgIUf/RP+g+9uFAKD4TB0R2ot6jZMJAwDQYJKoZIhvcNAQEL BQAwZjELMAkGA1UEBhMCVVMxCzAJBgNVBAgMAlZBMRIwEAYDVQQHDAlTb21ld2hl cmUxDjAMBgNVBAoMBU15T3JnMQ0wCwYDVQQLDARNeU9VMRcwFQYDVQQDDA5taW5p b3NlcnZlcnVidTAeFw0yMzAxMDYxNTA0MjhaFw0zMzAxMDMxNTA0MjhaMGYxCzAJ BgNVBAYTAlVTMQswCQYDVQQIDAJWQTESMBAGA1UEBwwJU29tZXdoZXJlMQ4wDAYD VQQKDAVNeU9yZzENMAsGA1UECwwETXlPVTEXMBUGA1UEAwwObWluaW9zZXJ2ZXJ1 YnUwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCcC0irpVFJQaVqiuKP LuRQUdYxehTvFCuCJLpGhS03XczCWKzemd0a3W/UC01N+tbFzpmCi0/l+R5SFsVC 3tRPKlngtDOICnYPpKSaWz03o3BgwNqtuKtucboXp3Qo+5DSd7XDi1qY+plY6LwC vbjLtjNBfyWlwS6nhTjDebvUY6w0xNmG8w1H9fPZcq/gsSGUnPyvywEENYmk5R6X xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1J/rLOJUVeKRvpbLraN25VoEoS8hz07DT3gmtVyJgpEK1oJ+mEs94ae76ujr5MGm M4HgV4+o9wxqjWdPyFDny/1EPsv30VqbcwmbkuPE7UHG4JH/5gxqxnXJcPnr3BQy 4GU= -----END CERTIFICATE----- The second configmap object is used to load mssql.conf, which contains the polybase Trace flag 13702, which is required for SQL Server on Linux. This configmap is generated using the yaml script shown below: # Create a yaml file called: mssql_config.yaml and save it to the location "C:\pbdemo\certs\mssql_config.yaml" kind: ConfigMap apiVersion: v1 metadata: name: mssql data: mssql.conf: | [EULA] accepteula = Y accepteulaml = Y [coredump] captureminiandfull = true coredumptype = full [language] lcid = 1033 [filelocation] defaultdatadir = /var/opt/mssql/userdata defaultlogdir = /var/opt/mssql/userlog [traceflag] traceflag0 = 13702 # We are ready to create the configmap using the command as shown below: Kubectl apply -f “C:\pbdemo\certs\ mssql_config.yaml” # You can inspect the configmap using the command shown below with the sample output as well C:\>kubectl describe configmap mssql Name: mssql Namespace: default Labels: <none> Annotations: <none> Data ==== mssql.conf: ---- [EULA] accepteula = Y accepteulaml = Y [coredump] captureminiandfull = true coredumptype = full [language] lcid = 1033 [filelocation] defaultdatadir = /var/opt/mssql/userdata defaultlogdir = /var/opt/mssql/userlog [traceflag] traceflag0 = 13702 Events: <none> The secret object in the cluster is used to save the sa password for SQL Server, the command to create the secret is as shown below kubectl create secret generic mssql --from-literal=MSSQL_SA_PASSWORD="MyC0m9l&xP@ssw0rd" Deploy SQL Server Containers We are now ready to deploy SQL Server containers, below is the deployment yaml called "sqldeploy.yaml" that is used to deploy one SQL Server container as a statefulset deployment using the Azure disk as the storage class. Using this YAML we deploy: One SQL Server instance, with separate PV(persistent volumes) and Persistent volume claims (PVCs) for User database, user log and tempdb files. We are loading the public.crt certificate to the folder: /var/opt/mssql/security/ca-certificates folder inside the container. We are loading the mssql.conf file with various parameter and the required trace flag 13702. At the end, we create the external load balancer Service to be able to connect to the SQL Server. # Create the sqldeploy.yaml at the location "C:\pbdemo\certs\sqldeploy.yaml", and paste the content below, you can modify it according to your requirements. kind: StorageClass apiVersion: storage.k8s.io/v1 metadata: name: azure-disk provisioner: kubernetes.io/azure-disk parameters: storageaccounttype: Standard_LRS kind: Managed --- apiVersion: apps/v1 kind: StatefulSet metadata: name: mssql labels: app: mssql spec: serviceName: "mssql" replicas: 1 selector: matchLabels: app: mssql template: metadata: labels: app: mssql spec: securityContext: fsGroup: 10001 containers: - name: mssql command: - /bin/bash - -c - cp /var/opt/config/mssql.conf /var/opt/mssql/mssql.conf && /opt/mssql/bin/sqlservr image: amvinacr.azurecr.io/sql-pb-update:latest ports: - containerPort: 1433 env: - name: ACCEPT_EULA value: "Y" - name: MSSQL_ENABLE_HADR value: "1" - name: MSSQL_SA_PASSWORD valueFrom: secretKeyRef: name: mssql key: MSSQL_SA_PASSWORD volumeMounts: - name: mssql mountPath: "/var/opt/mssql" - name: userdata mountPath: "/var/opt/mssql/userdata" - name: userlog mountPath: "/var/opt/mssql/userlog" - name: tempdb mountPath: "/var/opt/mssql/tempdb" - name: mssql-config-volume mountPath: "/var/opt/config" - name: mssql-tls-configmap mountPath: "/var/opt/mssql/security/ca-certificates/" volumes: - name: mssql-config-volume configMap: name: mssql - name: mssql-tls-configmap configMap: name: mssql-tls volumeClaimTemplates: - metadata: name: mssql spec: accessModes: - ReadWriteOnce resources: requests: storage: 8Gi - metadata: name: userdata spec: accessModes: - ReadWriteOnce resources: requests: storage: 8Gi - metadata: name: userlog spec: accessModes: - ReadWriteOnce resources: requests: storage: 8Gi - metadata: name: tempdb spec: accessModes: - ReadWriteOnce resources: requests: storage: 8Gi --- apiVersion: v1 kind: Service metadata: name: mssql-0 spec: type: LoadBalancer selector: statefulset.kubernetes.io/pod-name: mssql-0 ports: - protocol: TCP port: 1433 targetPort: 1433 name: tcpsql # You can run the below command to deploy SQL Server conatiner based on the deployment yaml file called sqldeploy.yaml Kubectl apply -f “C:\pbdemo\certs\sqldeploy.yaml” # You can run the kubectl get command to see all the SQL Server container deployed, sample command and output shown below for reference C:\>kubectl get all NAME READY STATUS RESTARTS AGE pod/mssql-0 1/1 Running 0 92m NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE service/kubernetes ClusterIP 10.0.0.1 <none> 443/TCP 16h service/mssql-0 LoadBalancer 10.0.252.15 20.235.55.7 1433:31551/TCP 13s NAME READY AGE statefulset.apps/mssql 1/1 97m Connect to SQL Server - Demo time We are ready to connect to the SQL Server, feel free to use your favorite SQL Server tool .viz SQL Server Management Studio(SSMS) or Azure Data Studio (ADS) and connect to the SQL Server and run the below commands to virtualize data and access CSV or parquet files and/or to take backups of the databases on the S3 based object storage. I'm using the SSMS tool and running the below queries. I connect to the SQL Server and confirm that that TLS certificate public.crt is loaded by SQL Server, I can do that from the errorlog using the command : sp_readerrorlog from the T-SQL edition and the sample output is shown below with the certificate loaded: Backup/Restore to S3-based object storage: To take S3 based backups, I need to first create bucket on the storage in this case, I connect to the MinIO portal and create the bucket called "sqltest" as shown below and then create a credential to access the storage. Now, use the below sample T-SQL script to create the credential and running the backup, the backup should be successful as shown below CREATE CREDENTIAL [s3://10.0.0.82:9000/sqltest] WITH IDENTITY = 'S3 Access Key', SECRET = 'amit:mypass12345'; BACKUP DATABASE S3_backup_demo TO URL = 's3://10.0.0.82:9000/sqltest/S3_backup_demo_db.bak' WITH STATS = 10 You can view the backup file created on the MinIO console as shown below: Data Virtualization- Polybase Here are the steps to configure data virtualization, below T-SQL sample script is used to configure the following steps: We verify the polybase feature is installed, then configure and enable polybase feature for SQL Server. Create the database scoped credential to access the object storage Now, we create the external data source and link it with the credential that we created in the preceding step. We are now ready to query the data from the external data source -- Verify the Polybase feature is installed: SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled; -- Enable polybase feature using the commmands below: exec sp_configure @configname = 'polybase enabled', @configvalue = 1; reconfigure with override exec sp_configure @configname = 'polybase enabled' --Next, let’s create a database and database scoped credential to access the object storage. create database pb_demo USE pb_demo CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypass123@'; CREATE DATABASE SCOPED CREDENTIAL s3_dc WITH IDENTITY = 'S3 Access Key', SECRET = 'amit:mypass12345' ; -- To verify, the credential is created you can run the below command: SELECT * FROM sys.database_scoped_credentials; -- Now go ahead and try creating the External data source: CREATE EXTERNAL DATA SOURCE s3_ds WITH (LOCATION = 's3://10.0.0.82:9000/',CREDENTIAL = s3_dc) -- When creating the external data source, if you see the below error, then please restart the container and run the same command again that should solve the below error Msg 46530, Level 16, State 11, Line 20 External data sources are not supported with type GENERIC. --We now have the credential and the external data source created to query files on the object storage to query a CSV based file that is already present on the object storage you can run the command as shown below SELECT * FROM OPENROWSET( BULK '/sqltest/test.csv', FORMAT = 'CSV', DATA_SOURCE = 's3_ds') WITH ( c1 varchar(50) ) AS [Test1] -- To query a parquet file that is available on the object storage you can run the below query, I copied a sample parquet file that is available here: https://github.com/Teradata/kylo/tree/master/samples/sample-data/parquet to my Minio object storage. SELECT * FROM OPENROWSET ( BULK '/sqltest/userdata1.parquet' , FORMAT = 'PARQUET' , DATA_SOURCE = 's3_ds' ) AS [Test12] Note: You can also use Create External Table as (CETAS) to access both parquet and CSV files for SQL Server on Linux. I hope you find this helpful to get started with polybase and S3 storage integration with SQL Server 2022 containers. Special thanks to my colleagues from the Polybase team for helping me with this demo. Happy learning!Announcing the Availability of adutil for RHEL 9 and Ubuntu 22.04 !
We’re thrilled to share that adutil, the Active Directory Utility for SQL Server, is now officially supported on RHEL 9 and Ubuntu 22.04. If you’ve been waiting for this, your patience has paid off! Let’s dive into the details. What Is adutil? adutil is a powerful tool that simplifies Active Directory integration with SQL Server on Linux-based deployments. It enables seamless authentication, user management, and keytab configuration. Whether you’re running SQL Server on RHEL 9 or Ubuntu 22.04, you can now harness the full capabilities of adutil. Getting Started Join Your Host Machine to the Domain: Before diving into adutil, ensure that your host machine is part of the domain. Follow the steps outlined in our article: Join SQL Server on Linux to Active Directory - SQL Server | Microsoft Learn. Installing adutil on RHEL 9: Execute the following commands to add the RHEL 9 Prod repository and install adutil: sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/9/prod.repo ## Now you are ready to install adutil using the command: sudo ACCEPT_EULA=Y yum install -y adutil ## you can check the adutil version using the command and ensure the version is 1.1.138 adutil --version Installing adutil on Ubuntu 22.04: Follow these steps to add the repository and install adutil: ## add the repo and required package signature curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list ## now update the repo and install adutil sudo apt-get update sudo ACCEPT_EULA=Y apt-get install -y adutil Explore adutil’s Features Account Creation: Easily create and manage user accounts tied to Active Directory. Keytab Configuration: Set up keytabs for secure authentication. User Management: Handle user permissions and access. For detailed guidance, refer to our comprehensive article: Introduction to adutil - Active Directory Utility - SQL Server | Microsoft Learn. Updated on 22nd November 2024: Streamlining User Account Security with Adutil: AES 128/256-bit Encryption Now Supported With the release of Adutil version 1.1.143 and later, you can now easily create and modify user accounts to enable or disable AES 128/256-bit encryptions for kerberos. This update, available starting from version 1.1.143, includes built-in support for Kerberos AES 128-bit and AES 256-bit encryption by default. A few samples to help you get started. ## You can see the -D parameter to diable the AES 128/256 encryption for the account, by default when you use the adutil create command it creates with these options set for the account $ adutil user create --help create - Creates a new AD user account Usage: create [name] Positional Variables: name Name of the user account to create Flags: --version Displays the program version string. -h --help Displays help with available flag, subcommand, and positional value parameters. -n --name Name of the user account to create --distname Distinguished name of the account -o --ouname Organizational Unit of the account (takes precedence over distinguished name) --upn OPTIONAL: User principal name for the created account --desc OPTIONAL: Description for the account being created --password OPTIONAL: Password for the user being created -D --disableaessupport OPTIONAL: Disable AES 128 and 256 support -d --debug Display additional debugging information when making LDAP/Kerberos calls. --accept-eula Accepts the current EULA for adutil. This has no effect if the EULA has already been accepted. If --ouname and --distname are omitted, the value set by `adutil config set ou` will be used ## Similarly you see the -E and -D command to enable and disable the AES account options respectively when using the adutil user modify command: $ adutil user modify --help modify - Modify an existing AD user account Usage: modify [name] Positional Variables: name Name of the user account to modify Flags: --version Displays the program version string. -h --help Displays help with available flag, subcommand, and positional value parameters. -n --name Name of the user account to modify --distname Distinguished name of the account -o --ouname Organizational Unit of the account (takes precedence over distinguished name) -E --enableaessupport OPTIONAL: Enable AES 128 and 256 support -D --disableaessupport OPTIONAL: Disable AES 128 and 256 support -d --debug Display additional debugging information when making LDAP/Kerberos calls. --accept-eula Accepts the current EULA for adutil. This has no effect if the EULA has already been accepted. If --ouname and --distname are omitted, the value set by `adutil config set ou` will be used ## When you use the adutil user create command as shown below the user is created with the AES 128/256 encryptions set for the account $adutil user create --name testuser --distname CN=testuser,CN=Users,DC=CONTOSO,DC=COM ## If you intend to create a user without this properties set then use the -D option as shown below $adutil user create --name AESdisableduser --distname CN=AESdisableduser ,CN=Users,DC=CONTOSO,DC=COM -D ## if you wish to modify the kerberos AES encryption for an account here is a sample for the same: $ adutil user modify --name testuser --distname CN=testuser,CN=Users,DC=CONTOSO,DC=COM -D Successfully disabled AES encryption for ('testuser', 'CONTOSO.COM') ## you could also enable the kerberos AES encryption for an account as shown below: $ adutil user modify --name aesdisableduser --distname CN=aesdisableduser,CN=Users,DC=CONTOSO,DC=COM -E Successfully enabled AES encryption for ('aesdisableduser', 'CONTOSO.COM') You can now use Adutil to modify existing user accounts to enable or disable these encryption options. This new feature eliminates the need for manually enabling these settings in Active Directory, as detailed in step #2 of this document Fun Fact: Ansible and adutil Did you know that Ansible can simplify your SQL Server deployment? The Ansible-based SQL Server collection leverages adutil behind the scenes to enable AD authentication for SQL Server on Linux. Explore more in the Red Hat Hybrid Cloud Console documentation: Automation Hub - microsoft.sql | Ansible Automation Platform (redhat.com) and our official documentation: Quickstart: Deploy SQL Server on Linux using an Ansible playbook - SQL Server | Microsoft Learn Get ready to enhance your SQL Server experience with adutil on RHEL 9 and Ubuntu 22.04! Thanks, Engineering Lead: Vaibhao Tatte Engineering Team: Dwaipayan Barman, Parameswara Reddy BodeddulaAlways on Availability Groups for SQL Server containers on Kubernetes - The DH2i's DxOperator way!!
Update as on 12 Feb 2024: The DH2i Operator is released on 6th Feb 2024, and generally available as per the latest blog from our Partner DH2i, I recommend you read this for further information: DxOperator for SQL Server Availability Group - DH2I. Recently, I had the privilege of presenting with OJ Ngo from DH2i, our partners who offer the cluster stack for SQL Server on Windows, Linux and containers, at PASS Data Community Summit 2023. In this session, we revealed the private preview of the DxOperator!! - A simple and effective option to deploy SQL Server containers with availability groups configured in high availability mode, with DxEnterprise cluster stack, all deployed on Kubernetes platform. Prerequisites A Kubernetes cluster to deploy SQL Server instances. For this demo, I am using Azure Kubernetes Service (AKS) cluster. A client machine to run the kubectl commands and manage the object creation and administration on the Kubernetes cluster, today I am going to use a windows machine and here are the instructions to setup kubectl to connect and manage AKS cluster. On the same client machine, I also have the SSMS (SQL Server Management Studio) or Azure Data Studio (ADS) installed to connect to SQL Server instances and view availability groups. What does this operator do? This operator allows you to complete all the following tasks in 5 commands and less than 5 minutes!! Deploy three or more SQL Server instances on a Kubernetes cluster with specific mssql-config parameters. Configure and install DxEnterprise cluster. Create and configure an empty SQL Server Always On availability group (AG) and join all the three or more SQL Server instances to this AG. You don’t believe me! Well do it with me to believe it. Do you have 5 minutes now? Yes, then setup the pre-requisites and let's get rolling: Step1: Create the configmap object on the Kubernetes cluster that has the SQL Server specific mssql-conf settings defined. Here is a sample script: # Create a file called mssqlconfig.yaml, with your specific mssql-conf settings added to it: apiVersion: v1 kind: ConfigMap metadata: name: mssql-config data: mssql.conf: | [EULA] accepteula = Y [network] tcpport = 1433 [sqlagent] enabled = true # Now run the command to create the object: kubectl apply -f "D:\operator\mssqlconfig.yaml" Step 2: Now, create a secret object to store the sa account password and another secret to store dxe passkey and license key, as shown below: # Create the secret to store the sa password for SQL Server. kubectl create secret generic mssql --from-literal=MSSQL_SA_PASSWORD= "MyP@SSw0rd1!" # Create the secret to store the license key for DH2i. You can get the developer license from -> https://dh2i.com/trial/. Replace the XXXX-XXXX-XXXX-XXXX with your License Key. kubectl create secret generic dxe --from-literal=DX_PASSKEY="MyP@SSw0rd1!" --from-literal=DX_LICENSE=XXXX-XXXX-XXXX-XXXX Step 3: Install the private preview operator using the following commands: # download the operator yaml from and save as v1beta2.yaml: dxoperator.dh2i.com/dxe/files/v1beta2.yaml # Now install the operator using the command: kubectl apply -f ”D:\operator\v1beta2.yaml” Step 4: Finally, provide the SQL Server and AG details like the name, limits and then watch the deployment happen: # create a file called crd_v1.yaml ( you can name it as you like) and copy the content as is, if you want to deploy 3 containers with the instance name contoso-sql-0, contoso-sql-1 and contoso-sql2 and configure AG between the three instances apiVersion: dh2i.com/v1beta2 kind: DxEnterpriseSqlAg metadata: name: contoso-sql spec: synchronousReplicas: 3 asynchronousReplicas: 0 # ConfigurationOnlyReplicas are only allowed with availabilityGroupClusterType set to EXTERNAL configurationOnlyReplicas: 0 availabilityGroupName: CONTOSOAG # For a contained availability group, add the option CONTAINED availabilityGroupOptions: null # Valid options are EXTERNAL (automatic failover) and NONE (no automatic failover) availabilityGroupClusterType: EXTERNAL createLoadBalancers: true template: spec: dxEnterpriseContainer: image: "docker.io/dh2i/dxe:beta" imagePullPolicy: Always acceptEula: true clusterSecret: dxe vhostName: VHOST1 joinExistingCluster: false # QoS – guaranteed (uncomment to apply) resources: limits: memory: 1Gi cpu: '1' # Configuration options for the required persistent volume claim forDxEnterprise volumeClaimConfiguration: storageClassName: null resources: requests: storage: 1Gi mssqlServerContainer: image: "mcr.microsoft.com/mssql/server:latest" imagePullPolicy: Always mssqlSecret: mssql acceptEula: true mssqlPID: Developer mssqlConfigMap: mssql-config # QoS – guaranteed (uncomment to apply) resources: limits: memory: 2Gi cpu: '2' # Configuration options for the required persistent volume claim for SQL Server volumeClaimConfiguration: storageClassName: null resources: requests: storage: 2Gi # Now deploy the custom resource using the command: kubectl apply -f "D:\operator\crd_v1.yaml" That’s it, now relax you’ve worked hard, wait for a few minutes, see the deployment happen, when it completes you see this: Step 5: Connect to your SQL Server primary instance to run the below T-SQL command to add a database “test” to your AG “CONTOSOAG” #T-SQL Command on the primary, create a database and back up the same to add it to the AG. create database test go backup database test to disk = '/var/opt/mssql/data/test.bak' #Add the newly created database to the availability group "CONTOSOAG" ALTER AVAILABILITY GROUP [CONTOSOAG] ADD DATABASE [test]; Once this step is completed view the cluster using AG dashboard as shown below: This is you regular AG deployed on Kubernetes with SQL Server containers based on your requirement!! Let us know what you think and how was your experience?
