In a world awash with data, the challenge lies in our ability to comprehend and engage with it seamlessly. My colleague Muazma has shed light on this topic in her insightful blog: “Chat with your data in Azure SQL Database,”.
Inspired by her work, I pondered the possibility of applying similar principles to SQL Server on Linux VMs hosted on Azure. This blog is a result of that contemplation and here are the steps that we cover in this blog:
- We’ll begin by setting up a Linux-based Virtual Machine on Azure and proceed to install SQL Server on it.
- Next, we’ll implement TLS 1.2 encryption to secure connections to SQL Server, utilizing certbot for certificate creation with Let’s Encrypt serving as the certificate authority.
- We’ll then import Kaggle’s dataset into SQL Server using the Import Flat File wizard.
- Following that, we’ll create the Azure AI search and associated indexes, with the Azure SQL Server on Linux VM as the data source.
- Lastly, we’ll utilize Azure OpenAI studio to interact with the data.
To follow my lead, all you need is an Azure Subscription and an account set up to gain access to Azure OpenAI Studio.
Step 1: Create the Azure SQL Server on Linux based VM:
Let’s start by setting up a Linux-based VM on Azure. For this demonstration, I’ll be configuring an Ubuntu 22.04 VM. Below is the script to first create a resource group, followed by the creation of a VM named SQLLinux22 running Ubuntu 22.04.
# let's create the resource group using the command:
az group create --name myrgdemo --location centralindia
#lets create VM using Ubuntu image, I am using this image: 0001-com-ubuntu-minimal-jammy
az vm create --resource-group myrgdemo --name sqllinux22 --size "Standard_B4ms" --location "central india" --image "Canonical:0001-com-ubuntu-minimal-jammy:minimal-22_04-lts:22.04.202405131" --admin-username "amvin" --admin-password "MY$trongPass123*#" --authentication-type all --generate-ssh-keys
Once the VM is set up, proceed to install SQL Server by following the guidelines provided in the official Microsoft documentation.
Step 2: Enable TLS 1.2 Encryption on SQL Server on Linux, to secure SQL Server connections:
Following the installation of SQL Server, it’s time to move on to step 2: enabling TLS 1.2 encryption to secure connections to SQL Server. But first, you need to set up a DNS name for the VM you’ve created, as it’s necessary for generating the certificate. You can configure the DNS name via the Azure portal. Once it’s set up, it will appear as shown. Remember to also enable port 80 in the VM’s Network Settings, which is required by Certbot for certificate creation.
With that completed, it’s now time to install Certbot and generate the necessary certificate. Log into the VM using your preferred SSH client and execute the following commands. These will install Certbot and then create the certificate using the DNS name you have set up.
amvin@sqllinux22:~$ sudo snap install --classic certbot
2024-05-16T21:11:23Z INFO Waiting for automatic snapd restart...
certbot 2.10.0 from Certbot Project (certbot-eff✓) installed
## After the installation go ahead and create the certificate and private key file.
amvin@sqllinux22:~$ sudo certbot certonly --standalone --key-type rsa --preferred-challenges http -d sqllinux22.centralindia.cloudapp.azure.com
Saving debug log to /var/log/letsencrypt/letsencrypt.log
Enter email address (used for urgent renewal and security notices)
(Enter 'c' to cancel): xxxxxxxxxxx
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Please read the Terms of Service at
https://letsencrypt.org/documents/LE-SA-v1.4-April-3-2024.pdf. You must agree in
order to register with the ACME server. Do you agree?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
(Y)es/(N)o: Y
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Would you be willing, once your first certificate is successfully issued, to
share your email address with the Electronic Frontier Foundation, a founding
partner of the Let's Encrypt project and the non-profit organization that
develops Certbot? We'd like to send you email about our work encrypting the web,
EFF news, campaigns, and ways to support digital freedom.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
(Y)es/(N)o: Y
Account registered.
Requesting a certificate for sqllinux22.centralindia.cloudapp.azure.com
Successfully received certificate.
Certificate is saved at: /etc/letsencrypt/live/sqllinux22.centralindia.cloudapp.azure.com/fullchain.pem
Key is saved at: /etc/letsencrypt/live/sqllinux22.centralindia.cloudapp.azure.com/privkey.pem
This certificate expires on 2024-08-14.
These files will be updated when the certificate renews.
Certbot has set up a scheduled task to automatically renew this certificate in the background.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
If you like Certbot, please consider supporting our work by:
* Donating to ISRG / Let's Encrypt: https://letsencrypt.org/donate
* Donating to EFF: https://eff.org/donate-le
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
## With this we have now created the required files as shown below:
root@sqllinux22:/etc/letsencrypt/live/sqllinux22.centralindia.cloudapp.azure.com# ll
total 28
drwxr-xr-x 2 root root 4096 May 16 21:13 ./
drwx------ 3 root root 4096 May 16 21:13 ../
-rw-r--r-- 1 root root 692 May 16 21:13 README
lrwxrwxrwx 1 root root 66 May 16 21:13 cert.pem -> ../../archive/sqllinux22.centralindia.cloudapp.azure.com/cert1.pem
lrwxrwxrwx 1 root root 67 May 16 21:13 chain.pem -> ../../archive/sqllinux22.centralindia.cloudapp.azure.com/chain1.pem
lrwxrwxrwx 1 root root 71 May 16 21:13 fullchain.pem -> ../../archive/sqllinux22.centralindia.cloudapp.azure.com/fullchain1.pem
lrwxrwxrwx 1 root root 69 May 16 21:13 privkey.pem -> ../../archive/sqllinux22.centralindia.cloudapp.azure.com/privkey1.pem
Move the certificate and necessary files to the “/var/opt/mssql/secrets” directory for SQL Server’s use and to enable TLS 1.2 encryption as demonstrated below. After enabling TLS 1.2 encryption, please restart SQL Server.
# copy the cert and key to the secrets folder as shown below, we are converting the key # from .pem format to .key using the openssl option.
sudo cp /etc/letsencrypt/live/sqllinux22.centralindia.cloudapp.azure.com/fullchain.pem /var/opt/mssql/secrets/fullchain.pem
sudo openssl rsa -in /etc/letsencrypt/live/sqllinux22.centralindia.cloudapp.azure.com/privkey.pem -out /var/opt/mssql/secrets/privkey.key
# Enable TLS 1.2 as shown below using the mssql-conf for SQL Server
sudo /opt/mssql/bin/mssql-conf set network.tlscert /var/opt/mssql/secrets/fullchain.pem
sudo /opt/mssql/bin/mssql-conf set network.tlskey /var/opt/mssql/secrets/privkey.key
sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2
sudo /opt/mssql/bin/mssql-conf set network.forceencryption 0
# Restart SQL Server and confirm that TLS 1.2 is enabled as seen in the errorlog:
amvin@sqllinux22:~$ sudo systemctl restart mssql-server
# Now, lets read the errorlog, to confirm the certificate is loaded
root@sqllinux22:~# cat /var/opt/mssql/log/errorlog | grep "Allowed TLS"
2024-05-16 21:23:16.78 Server Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.2']. Allowed TLS ciphers are ['ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA:!DHE-RSA-AES256-GCM-SHA384:!DHE-RSA-AES128-GCM-SHA256:!DHE-RSA-AES256-SHA:!DHE-RSA-AES128-SHA'].
Step 3: Load the dataset into SQL Server using the Import Flat file Wizard:
Now that we’ve reached the third step, it’s time to load the dataset into SQL Server on the Linux Azure VM. I connected to the SQL Server Azure VM using SQL Server Management Studio (SSMS) on my Windows machine and downloaded the dataset locally.
Next, I utilized the SSMS import flat file wizard to transfer the data from the file to SQL Server on the Azure VM. Once the dataset is loaded into the table, the data is displayed as follows.
Step 4: Create the Azure AI search service, Index and import data from SQL Server:
With data loaded, let's log in to the Azure Portal, search for AI search -> click on create and create the search service as shown below:
once, the search service is created, we need to link it to the Azure SQL Server VM to import the data into the search. During the import process, it’s crucial to ensure that you map the correct data type. In this instance, I chose the string data type for all columns during the import. Additionally, take note of the essential facets of the columns that I have enabled as shown below.
After the index is created and the data is imported, it should appear as follows, and you can also execute a sample query to confirm that you can retrieve data as illustrated below:
Step 5: Use Azure OpenAI Studio to chat with your data:
That's it, we are now in the final stage where using Azure OpenAI studio I can create a "Chat playground AI model", configure the DataSource to the Azure AI search, index that I created above, and you are now ready to chat with your data as shown below. Try asking scenario and context-based questions like "What products to buy for a kid's birthday?", "Suggest items to buy for decor of a room".
Hope you enjoyed reading this! Happy Friday!