Connecting to a database in a cluster SQL Server

Getting and installing an SSL certificate
Sample connection strings

You can connect to Managed Service for SQL Server cluster hosts:

Over the internet, if you configured public access for the appropriate host. You can only connect to such clusters over an SSL connection.
From Yandex.Cloud VM instances hosted in the same virtual network. If the cluster isn't publicly available, you don't need to use an SSL connection to connect to such VMs.

Getting and installing an SSL certificate

To use an encrypted SSL connection, get an SSL certificate and add it to the operating system's trusted certificate store:

Ubuntu 20.04

$ sudo mkdir -p /usr/local/share/ca-certificates/Yandex && \
sudo wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" -O /usr/local/share/ca-certificates/Yandex/YandexCA.crt && \
sudo update-ca-certificates

Sample connection strings

Examples were tested in the following environment:

Virtual machine in Yandex.Cloud running Ubuntu 20.04 LTS.
Bash: 5.0.16.
mono-complete: 6.8.0.105.
Python: 3.8.2; pip3: 20.0.2.
OpenJDK: 11.0.8; Maven: 3.6.3.
PHP: 7.4.3.

To view an example of the command with the host FQDN filled in, open the cluster page in the management console and click Connect.

Bash

Bash (SSL)

C# (SSL)

Java

Java (SSL)

Go (SSL)

ODBC

ODBC (SSL)

Install the dependencies:

$ sudo apt update && \
sudo apt install python3-pip python-is-python3 && \
pip3 install mssql-cli && \
source ~/.profile

By default, Microsoft collects anonymous data about the use of the mssql-cli client utility. For more information, see the mssql-cli documentation. To disable automatic data collection, run:
```
$ export MSSQL_CLI_TELEMETRY_OPTOUT=True
```
Connect to a database:
```
$ mssql-cli -U <username> \
          -d <database name> \
          -S <FQDN of the host>,1433
```
After running the command, enter the user password to complete the connection procedure.
To check that the connection is successful, request the SQL Server DBMS version:
```
SELECT @@version;
```

Install the dependencies:

$ sudo apt update && \
sudo apt install python3-pip python-is-python3 && \
pip3 install mssql-cli && \
source ~/.profile

By default, Microsoft collects anonymous data about the use of the mssql-cli client utility. For more information, see the mssql-cli documentation. To disable automatic data collection, run:
```
$ export MSSQL_CLI_TELEMETRY_OPTOUT=True
```
Connect to a database:
```
$ mssql-cli -U <username> \
          -d <database name> \
          -S ,1433 \
          -N
```
After running the command, enter the user password to complete the connection procedure.
To check that the connection is successful, request the SQL Server DBMS version:
```
SELECT @@version;
```

Install the dependencies:

$ sudo apt update && \
sudo apt install mono-complete nuget && \
sudo nuget update -self && \
nuget install System.Data.SqlClient

Code example:

connect.cs

using System;
using System.Data.SqlClient;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            var server = "<host FQDN>";
            var database = "<database name>";
            var uid = "<username>";
            var pwd = "<user password>";

            var connectionString = String.Format("Server={0},1433;Database={1};Uid={2};Pwd={3};Trusted_Connection=no;Encrypt=no;", server, database, uid, pwd);
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                SqlCommand command = new SqlCommand("SELECT @@version;", connection);
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader[0]);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            finally
            {
                connection.Close();
            }
        }
    }
}

Building and connecting:
```
$ mcs connect.cs -r:System.Data.dll && \
mono connect.exe
```
If the connection to the cluster and the test query are successful, the SQL Server version is output.

Install the dependencies:

$ sudo apt update && \
sudo apt install mono-complete nuget && \
sudo nuget update -self && \
nuget install System.Data.SqlClient

Code example:

connect.cs

using System;
using System.Data.SqlClient;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            var server = "<host FQDN>";
            var database = "<database name>";
            var uid = "<username>";
            var pwd = "<user password>";

            var connectionString = String.Format("Server={0},1433;Database={1};Uid={2};Pwd={3};Trusted_Connection=no;Encrypt=yes;", server, database, uid, pwd);
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                SqlCommand command = new SqlCommand("SELECT @@version;", connection);
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader[0]);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            finally
            {
                connection.Close();
            }
        }
    }
}

Building and connecting:
```
$ mcs connect.cs -r:System.Data.dll && \
mono connect.exe
```
If the connection to the cluster and the test query are successful, the SQL Server version is output.

Install the dependencies:
```
$ sudo apt install default-jdk maven
```

Create a folder for the Maven project:

$ cd ~/ && mkdir -p project/src/java/com/example && cd project/

Create a configuration file for Maven:

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project
    xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>app</artifactId>
    <packaging>jar</packaging>
    <version>0.1.0</version>
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>8.4.0.jre11</version>
        </dependency>
	  </dependencies>
	<build>
    	<finalName>${project.artifactId}-${project.version}</finalName>
    	<sourceDirectory>src</sourceDirectory>
		<resources>
    		<resource>
   	     	<directory>src</directory>
    		</resource>
    	</resources>
   	<plugins>
   		<plugin>
       		<groupId>org.apache.maven.plugins</groupId>
       		<artifactId>maven-assembly-plugin</artifactId>
       		<executions>
       			<execution>
       				<goals>
   	    				<goal>attached</goal>
       				</goals>
   	    			<phase>package</phase>
   	    			<configuration>
   		    			<descriptorRefs>
   			    			<descriptorRef>jar-with-dependencies</descriptorRef>
   		    			</descriptorRefs>
   			    		<archive>
   			    			<manifest>
   			    				<mainClass>com.example.App</mainClass>
   			    			</manifest>
   	    				</archive>
   	    			</configuration>
   	    		</execution>
	    		</executions>
   		</plugin>
			<plugin>
    			<groupId>org.apache.maven.plugins</groupId>
       		<artifactId>maven-jar-plugin</artifactId>
    			<version>3.1.0</version>
   			<configuration>
       			<archive>
       				<manifest>
        					<mainClass>com.example.App</mainClass>
    	     			</manifest>
   	    		</archive>
   			</configuration>
   		</plugin>
   	</plugins>
   </build>
</project>

Code example:

src/java/com/example/App.java

package com.example;

import java.sql.*;

public class App {

    public static void main(String[] args) {
        String server = "<FQDN of the host>";
        String database = "<database name>";
        String user = "<username>";
        String password = "<user password>";

        String connectionString = String.format(
                "jdbc:sqlserver://%s:1433;" +
                "database=%s;" +
                "user=%s;" +
                "password=%s;" +
                "encrypt=false;" +
                "trustServerCertificate=false;" +
                "loginTimeout=30;",
                server, database, user, password);
        try {
            Connection connection = DriverManager.getConnection(connectionString);
            ResultSet q = connection.createStatement().executeQuery("SELECT @@version;");
            if(q.next()) {System.out.println(q.getString(1));}
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

Building and connecting:
```
$ mvn clean package && \
java -jar target/app-0.1.0-jar-with-dependencies.jar
```
If the connection to the cluster and the test query are successful, the SQL Server version is output.

Install the dependencies:
```
$ sudo apt install default-jdk maven
```
Add the SSL certificate to the Java trusted certificate store (Java Key Store) so that the SQL Server driver can use this certificate for secure connections to the cluster hosts. Make sure to set the password using the -storepass parameter for additional storage protection:
```
$ cd /etc/security && \
sudo keytool -importcert -alias YandexCA -file /usr/local/share/ca-certificates/Yandex/YandexCA.crt -keystore ssl -storepass <certificate store password> --noprompt
```

Create a folder for the Maven project:

$ cd ~/ && mkdir -p project/src/java/com/example && cd project/

Create a configuration file for Maven:

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project
    xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>app</artifactId>
    <packaging>jar</packaging>
    <version>0.1.0</version>
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>8.4.0.jre11</version>
        </dependency>
	  </dependencies>
	<build>
    	<finalName>${project.artifactId}-${project.version}</finalName>
    	<sourceDirectory>src</sourceDirectory>
		<resources>
    		<resource>
   	     	<directory>src</directory>
    		</resource>
    	</resources>
   	<plugins>
   		<plugin>
       		<groupId>org.apache.maven.plugins</groupId>
       		<artifactId>maven-assembly-plugin</artifactId>
       		<executions>
       			<execution>
       				<goals>
   	    				<goal>attached</goal>
       				</goals>
   	    			<phase>package</phase>
   	    			<configuration>
   		    			<descriptorRefs>
   			    			<descriptorRef>jar-with-dependencies</descriptorRef>
   		    			</descriptorRefs>
   			    		<archive>
   			    			<manifest>
   			    				<mainClass>com.example.App</mainClass>
   			    			</manifest>
   	    				</archive>
   	    			</configuration>
   	    		</execution>
	    		</executions>
   		</plugin>
			<plugin>
    			<groupId>org.apache.maven.plugins</groupId>
       		<artifactId>maven-jar-plugin</artifactId>
    			<version>3.1.0</version>
   			<configuration>
       			<archive>
       				<manifest>
        					<mainClass>com.example.App</mainClass>
    	     			</manifest>
   	    		</archive>
   			</configuration>
   		</plugin>
   	</plugins>
   </build>
</project>

Code example:

src/java/com/example/App.java

package com.example;

import java.sql.*;

public class App {

    public static void main(String[] args) {
        String server = "<FQDN of the host>";
        String database = "<database name>";
        String user = "<username>";
        String password = "<user password>";

        String connectionString = String.format(
                "jdbc:sqlserver://%s:1433;" +
                "database=%s;" +
                "user=%s;" +
                "password=%s;" +
                "encrypt=true;" +
                "trustServerCertificate=false;" +
                "loginTimeout=30;",
                server, database, user, password);
        try {
            Connection connection = DriverManager.getConnection(connectionString);
            ResultSet q = connection.createStatement().executeQuery("SELECT @@version;");
            if(q.next()) {System.out.println(q.getString(1));}
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

Building and connecting:
```
$ mvn clean package && \
java -jar target/app-0.1.0-jar-with-dependencies.jar
```
If the connection to the cluster and the test query are successful, the SQL Server version is output.

Install the dependencies:

$ go get github.com/denisenkom/go-mssqldb

Code example:

import (
    _ "github.com/denisenkom/go-mssqldb"
    "database/sql"
)

connStr := `
        server=<FQDN of the host>;
        port=1433;
        database=<database name>;
        user id=<username>;
        password=<user password>
`
conn, err := sql.Open("sqlserver", connStr)

Install the dependencies:

$ go get github.com/denisenkom/go-mssqldb

Code example:

import (
    _ "github.com/denisenkom/go-mssqldb"
    "database/sql"
)

connStr := `
        server=<FQDN of the host>;
        port=1433;
        database=<database name>;
        user id=<username>;
        password=<user password>;
        encrypt=true;
        TrustServerCertificate=false
`
conn, err := sql.Open("sqlserver", connStr)

Install the dependencies:

$ sudo apt install unixodbc unixodbc-dev && \
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - && \
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list && \
sudo apt update && \
sudo ACCEPT_EULA=Y apt install msodbcsql17

Code example:

[sqlserver]

Driver={ODBC Driver 17 for SQL Server};
Server=<FQDN of the host>,1433;
Database=<database name>;
Uid=<username>;
Pwd=<user password>;
Encrypt=no;
TrustServerCertificate=no;
Connection Timeout=30;

Install the dependencies:

$ sudo apt install unixodbc unixodbc-dev && \
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - && \
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list && \
sudo apt update && \
sudo ACCEPT_EULA=Y apt install msodbcsql17

Code example:

[sqlserver]

Driver={ODBC Driver 17 for SQL Server};
Server=<FQDN of the host>,1433;
Database=<database name>;
Uid=<username>;
Pwd=<user password>;
Encrypt=yes;
TrustServerCertificate=no;
Connection Timeout=30;