Microsoft SQL Server, also known as MSSQL or just “SQL Server”, is a proprietary database server developed by Microsoft. Ultorg has dedicated support for Microsoft SQL Server and its particular dialect of SQL (Transact-SQL).

Connecting

To connect Ultorg to Microsoft SQL Server, click Add Data Source→Connect to Database.

Then select Microsoft SQL Server from the Driver dropdown, and click Next.

Then, enter your connection details.

The connection-related fields are as follows:

• Host: The host name or IP address of the server.

  If you are using an SSH tunnel, follow these instructions and enter localhost here.

• Port: The port number to connect to; typically 1433.
• Database: The name of the database to connect to. This field can usually be left empty, as Ultorg shows all databases and schemas by default.
• Instance Name: The instance to connect to. Leave empty to connect to the server's default instance (common).
• User Name: The user name to log in with. For windows authentication, leave blank.
• Password: The password to log in with. For windows authentication, leave blank.
• Connection Properties: Click to set additional parameters that may be required to authenticate your connection. See the official JDBC driver documentation here.

  To make the connection work, you will need to do one of the following:

  • Add the property trustServerCertificate with the value true.
  • Or, for protection against man-in-the-middle attacks, follow the additional instructions below to configure SSL certificates.

• JDBC URL: A connection string constructed from the Host, Port, Database, and Instance Name fields.

You can click Test Connection to verify your settings.

If you get a PKIX path building failed or the driver could not establish a secure connection error, ensure that you added the trustServerCertificate=true property, or configured SSL.

Last, click Finish. In the Folders sidebar, your data source () will appear with available tables () underneath. If your server has multiple databases and/or schemas, the tables will appear in folders ().

Double-click a table to open it in a new perspective.

Integrated Windows Authentication

For Windows clients, some servers may support passwordless login using the credentials of the currently logged-in Windows user. This is useful e.g. in corporate environments that use Active Directory for single sign-on (SSO).

To connect to Microsoft SQL Server using integrated Windows authentication, set the integratedSecurity property to true (from the Ultorg Connect to Database dialog).

Equivalently, you can add ;integratedSecurity=true to the end of the JDBC URL. E.g. for testing on your local machine:

jdbc:sqlserver://localhost:1433;trustServerCertificate=true;integratedSecurity=true

The required DLL file comes bundled with Ultorg 1.6.2 and later.

SSL Configuration

Connections to Microsoft SQL Server are encrypted by default, using the SSL protocol.

Basic SSL encryption can be made to work immediately by setting the trustServerCertificate connection property to the value true. This does not, however, protect against man-in-the-middle attacks, where another computer pretends to be the server. This requires the client to verify the server's identity, which we cover next.

Validating the Server's Identity

To verify the server's identity on each connection attempt:

1. Get the trusted root certificate, or multiple trusted root/intermediate certificates, that the client should use to verify the certificate presented by the server.

   The certificate(s) must be stored in a file in one of the following formats:

   • PKCS#12: A binary file typically with extension pfx or p12.
   • Java KeyStore: A binary file typically with extension jks.

   If your certificate is in the PEM file format (a plain-text file with the first line containing the words BEGIN CERTIFICATE), convert it to JKS using the instructions below.

   On Amazon RDS, the root certificates are provided in a file called global-bundle.pem, available here. Convert the file from PEM to JKS using the instructions below.

2. From the Ultorg Connect to Database dialog, set the following connection properties, as documented here:
   • trustStore: The path to the JKS or PKCS#12 file from step 1. The file extension is jks, pfx, or p12.
   • trustStorePassword: The password that protects the trustStore file, if any.

     (If the trust store file contains only public certificates, a password is not really necessary. But the tools that generate these files will often insist on setting a password anyway.)

   • hostNameInCertificate: The host name to expect and validate in the server's certificate. This property only needs to be specified if it is different from the host name that is used to connect to the server.

   Ensure that the trustServerCertificate property is not specified.

3. Click Test Connection to check that the connection works.

Converting PEM Certificates

If the certificates you need for SSL configuration are in the PEM format, you will need to convert them to a Java KeyStore (jks) file, as follows:

1. If the PEM file contains multiple certificates (lines starting BEGIN CERTIFICATE), split it up into individual PEM files each containing one certificate. You can do this with a text editor, or automatically on Linux with the following command:

   cat my-certificates.pem | \
     awk 'split_after == 1 {n++;split_after=0} \
     /-----END CERTIFICATE-----/ \
     {split_after=1}{print > "onecert" n ".pem"}'
   

   (Where my-certificates.pem is the name of the PEM file containing multiple certificates.)

2. Use the keytool command from the Java Development Kit to import each single-certificate PEM into a Java KeyStore file. For example:

   keytool -import -file onecert.pem  -alias ca0 \
     -keystore myroots.jks -noprompt -storepass passwd
   keytool -import -file onecert1.pem -alias ca1 \
     -keystore myroots.jks -noprompt -storepass passwd
   keytool -import -file onecert2.pem -alias ca2 \
     -keystore myroots.jks -noprompt -storepass passwd
   

   Or, in a single Linux command:

   find . -name 'onecert*.pem' -exec \
     keytool -import -file {} -alias {} \
     -keystore myroots.jks -noprompt -storepass passwd \;
   

3. You can now set the trustStore connection property to point to the myroots.jks file, and set the trustStorePassword connection property to passwd.

Other Notes

• Ultorg has been tested with SQL Server 2022 for Linux, SQL Server 2017 for Windows, and SQL Server 2014 for Windows.

See Also

• Connecting through an SSH Tunnel.
• Connecting to Microsoft SQL Server databases hosted on Amazon RDS.