Home > Workload Solutions > SQL Server > Guides > Design Guide—SQL Server 2022 Database Solution with Object Storage on Dell Hardware Stack > Certificate creation and installation
End to end encrypted communication between the ECS storage and the SQL Server 2022 instance is required to perform data virtualization. A local certificate authority was created and used to generate and sign a private certificate validating the authenticity of communication between the two interfaces.
openssl genrsa -des3 -out certs/rootCA.key 2048
[req]
default_bits = 2048
prompt = no
default_md = sha256
distinguished_name = dn
[dn]
C=US
ST=Durham
L=Durham
O=Dell
OU=Bizapp
emailAddress=admin@proddc.sql
CN = sqlpool.proddc.sql
openssl req -x509 -new -nodes -key certs/rootCA.key -sha256
-days 1460 -out certs/rootCA.pem -config server.csr.cnf
Move the rootCA.pem file to a Windows host running a SQL Server instance. Open a PowerShell session as administrator and run the following command to add the provided certificate to the Windows “ROOT” certificate store.
certutil -addstore -f "ROOT" rootCA.pem
openssl x509 -in rootCA.pem -inform PEM -out rootCA.crt
/bin/update-ca-trust
The process of mapping a certificate to a containerized environment is different from traditional OS environments. A ConfigMap based on the root certificate must be created and then mapped as a volume in the deployment script. This process is expanded upon in the SQL Server 2022 Deployment section.
openssl req -new -sha256 -nodes -out certs/server.csr -newkey rsa:2048 -keyout certs/server.key -config server.csr.cnf
authorityKeyIdentifier=keyid,issuer
basicConstraints=CA:FALSE
keyUsage = digitalSignature, nonRepudiation,
keyEncipherment, dataEncipherment
subjectAltName = @alt_names
[alt_names]
DNS.1 = sqlpool.proddc.sql
IP.1 = 10.230.87.43
openssl x509 -req -in certs/server.csr -CA certs/rootCA.pem -CAkey certs/rootCA.key -CAcreateserial -out certs/server.crt -days 1460 -sha256 -extfile v3.ext
cat certs/server.key certs/server.crt > certs/combined.pem
Once the certificate generation has been completed, the front-end definition in the HAProxy configuration file, “haproxy.cfg,” can be defined for HTTPS.
frontend https-in
bind *:443 ssl crt /etc/haproxy/combined.pem
reqadd X-Forwarded-Proto:\ https
# Define the hostnames
acl host_s3 hdr(host) -i -m dom sqlpool.proddc.sql
acl host_s3_ip hdr(host) -i -m dom 10.230.87.43
# Route to backend
use_backend s3_backend if host_s3
use_backend s3_backend if host_s3_ip
In this example, SSL is terminated at the HAProxy load balancer and thus certificates do not need to be created for the ECS nodes and the non-SSL ports of the ECS nodes will be used as defined in the HTTP backend section of the haproxy.cfg configuration file.
Once the changes in “haproxy.cfg” are processed, verify the configuration file, and restart the HAProxy service to activate the certificate and load balancing directives defined for “https-in”.
There are multiple ways to validate encrypted communication through HTTPS to the Dell ECS storage cluster. In the use case section, SQL Server is used to access objects present on ECS storage with S3. However, to quickly validate that the communication is working, use the free “S3 Browser” application.
Figure 10. Connection settings for S3 Browser
The Access Key ID and Secret Access Key that are obtained from Dell ECS storage along with the rest endpoint for the HAProxy LoadBalancer are used for this communication. Make sure to select “Use secure transfer (SSL/TLS)”.
Connect to ECS storage and browse through the buckets and objects within the applicable namespaces once an account has been created.