Many developers who use SQL Server are used to the security and ease of using Windows-authenticated connections. If their job changes such that they switch to a non-Windows workstation operating system, such as Linux or macos, but still need to connect to SQL Server, they now need to switch to SQL Authentication. This means having a different account (and therefore, hopefully a different password) on every SQL Server they use. Also, if password expiration policies are inherited from an Active Directory domain, all of those passwords will expire, and possibly not at the same time. And worst of all, some companies will not even allow SQL Authentication, so the developers using Linux and macos have to figure out a way to connect to SQL Server using Windows Authentication when they are not using Windows.
Many use some type of virtualization to run "just enough Windows" to establish Windows Authentication to SQL Server. However, even "just enough" is a pretty heavy-handed approach. It requires installing SQL Server client tools, and registering with your company's Active Directory in order to be policy- and patch-compliant.
For many servers, there is another way, and this gist will show you how to do it. JetBrains DataGrip allows you to use Windows Authentication to connect to SQL Server from Windows, Linux, or macos. There are a couple of pre-requisites, however:
- First, you have to acquire DataGrip itself. It is a bit pricey, and there is no free "community edition."
- Second, your SQL Server instance has to have a properly-configured Kerberos Service Principal Name (SPN) configured.
Configuring an SPN is outside the scope of this gist. If you need information on how to configure a Service Principal Name (SPN) for SQL Server, here are some resources from Microsoft that may help you.
- Register a Service Principal Name for Kerberos Connections
- Manual SPN Registration
- Microsoft® Kerberos Configuration Manager for SQL Server®
Assuming the SPN is set up and configured correctly, this procedure will allow you to use DataGrip to connect to a SQL Server using Windows Authentication.
- To start, open a DataGrip project. If you need to, create a new DataGrip project and make sure it is open after you create it.
- If you have not used DataGrip before, you may not be able to proceed for a short period while the program performs some internal indexing. There will be a status message at the bottom right informing you of this indexing activity.
- Click File | New | Data Source | Microsoft SQL Server. This should open the Data Sources and Drivers interface.
Make sure you are viewing the General section of the interface and then perform the following steps.
-
Set the Name and Comment fields to anything you'd like. This name is just a label that will help you identify the connection later.
-
Set the Driver: to Microsoft SQL Server (jTds).
-
For Host, enter the hostname or IP address of the server you want to connect to.
-
Enter a value for Port if you need to. If you are unsure, ask your database support team.
-
Enter a value for Instance if you need to. If you are unsure, ask your database support team.
-
For Authentication, choose Windows credentials.
If you do not have an SPN configured, you can still use DataGrip to connect to SQL Server using SQL Authentication by choosing user and password as your authentication method.
-
For Database, enter the name of the database you want the connection to initially connect to.
-
If you have never used the Microsoft SQL Server (jTds) driver in DataGrip before, look for a link near the bottom of the screen offering to download it for you. Click it and it will be installed for you quickly and automatically.
At this point, the General section should look something like the screenshot below. Note that you may not be able to test the connection to your datasource until after the Advanced section is also configured.
Click on the word Advanced, which is just underneath the Name and Comment fields at the top of the interface, to make sure you are viewing the Advanced section of the interface. Then use the Advanced interface to set the following property values if they are needed. I have included notes to help you understand if you need to set these values. If you are unsure, set them. If you are unsure about being unsure, ask your database support team.
Some of the properties may already be listed in the interface. If they are, just replace their values appropriately. If a property is not already listed, add it as a user-defined property. You may have to scroll to the bottom of the property list to add user-defined properties.
| Property Name | Property Value | Notes |
|---|---|---|
| SSL | require | see note 1 |
| DOMAIN | mycorp.com | see note 2 |
Notes:
- Note 1: SSL should be mandatory, but it's not always. If it is not mandatory where you work, I think that you should enable it for your connections anyway.
- Note 2: Your Active Directory domain name is required for Windows Authentication.
| Property Name | Property Value | Notes |
|---|---|---|
| AUTOCOMMIT | false | see note 3 |
Notes:
- Note 3: I always have people disable this parameter unless they are absolutely certain they understand how their provider (in this case, jTds) behaves when it is enabled, and absolutely certain that their application will behave correctly when it is enabled. Different providers implement this setting in different ways, and most seem to implement it differently than SQL Servers's default transaction handling methods. This can make your application behave in surprising ways, or worse, block your own application or other applications using the same database.If you are comfortable with these risks, don't blame me. If you are unsure, ask your database support team. Or, be one of the cool kids and just leave it disabled.
Switch back to the General section and click the Test Connection link. If all goes well, you should get a success message, and should be able to begin to query this data source. Congratulations, you've just connected to SQL Server using Windows Authentication in a way that works from Windows, macos, and Linux!
