Connecting DataGrip to MS SQL Server

Some specific needs of SQL developers like connecting to SQL Server from OSX and even using Windows authentication can be covered by DataGrip. Since releasing DataGrip, we’ve received lots of comments about connecting to SQL Server. It appears it’s not straightforward as with other databases, requiring a couple of extra steps beforehand. Here is a small guide describing the main problems you may face and how to solve them.

First, ensure that important options in SQL Server Configuration Manager are configured correctly. Make sure the SQL Server Browser is running:

SQLServer Browser

TCP/IP connections need to be enabled:

tcp

To know the port for the connection just click on TCP/IP. In our case, it’s default port 1433.

Port

If you end up changing anything, restart the server. For most situations, this will resolve connection problems, your server will accept remote connections and you are ready to go. If you still have problems, it could be that TCP port needs to be adjusted or the remote connection to SQL server needs to be enabled in its settings. This tutorial will walk you through it, but we’ll also describe the process here.

Invoke Database View by pressing Alt/Cmd+1 and create a new data source with the appropriate driver. If you use a Microsoft driver, note that it supports SQL Server 2008 and later versions. Here we’ll assume it’s the jTds driver.

NewDataSource

If you have no driver, download it by clicking the link.

Download drivers

Then enter the host name, the instance name and the credentials. Remember that DataGrip offers completion for host, instance and database names (Ctrl/Cmd+Space), but the name of the database is optional. For those who are used to the SSMS interface, let’s compare the connection windows.

Connect SSMS Compare

If the port number conflicts with the instance name, in the jTds driver, the instance name will take precedence over the port number, while the opposite will happen in the Microsoft driver. To avoid confusion, simply remove the port number. If you strongly wish to specify the port number, click the Auto-set button (located to the right of the Instance field) to have the port number set automatically. But remember that the port number can be changed in case you restart the server.

PortConflict

If you need to use Windows authentication, only Microsoft driver will let you do it.

Windows auth

The other way to connect to the server with Windows authentication is to use domain authentication of jTds driver, which lets you connect from any other machine. What is notable, it works on any OS. To do this, go to the Advanced tab of data source properties, set USENTLMV2 to true and specify the domain name in the DOMAIN field.

AdvancedForWindows

Then enter your Windows credentials in user/password fields and click Test Connection.

If you want to check which particular databases or schemas are shown in the database view, go to the Schemas tab in Data source properties:

SchemasTab

In case you are already using DataGrip 2016.2 EAP, use More Schemas… in the database view:

MoreSChemas

All selected databases will appear in the database view. This is how it looks in SQL Server Management Studio:

Databases

And here’s how it looks in DataGrip:

ManyDBSCurrent

That’s it! If you are experiencing any other problems, please tweet @ us or create an issue in our youtrack.

Your DataGrip Team

The Drive to Develop

This entry was posted in Features, Tutorial and tagged , . Bookmark the permalink.

62 Responses to Connecting DataGrip to MS SQL Server

  1. Pingback: DataGrip 2016.2 is Out! | JetBrains DataGrip Blog

  2. Matt says:

    I had to get the port number from “SQL Server Config Manager | … | TCP Properties | IP ALL” at the bottom. That’s from clicking the TCP

  3. Al H. says:

    In my case, I was connecting from Mac OS and used the jTds driver and Windows Authentication. I was not successful until I entered my domain in the Advanced -> DOMAIN setting. Entering the server name (as specified in the article) did not work for me.

  4. Bill says:

    how to get the “Jobs” under “SQL server Agent”?
    tks

  5. Toby says:

    I found it does not support MS SQL Server 2016, is there any plan to fix?

  6. Cain says:

    I am using DataGrip 2017.1.4 and there is no option for Windows authentication using the SQL Server Microsoft driver. Please assist?

  7. Mahesh says:

    Datagrid doesnt seem to support Begin/End. For instance

    IF NOT EXISTS (SELECT * FROM mytable WHERE my_id = 145)
    BEGIN
    select * from mytable
    END

    Is there a workaround for this issue? Also DG seem to like putting double quotes around table and column names. Is there a way to prevent that?

  8. Shinya Koizumi says:

    Why configuring MSSQL local db is so complicated? DG works great with Remote MS SQL db but never able to figure out how to connect local db. Should not be more than filling HOST, INSTANCE,DATABASE. Connection fail all the time.

  9. Prashant Karehonna says:

    I am trying to connect to MSSQL Server 2014 AlwaysOn Availabilty Group (AAG) Listener using datagrip and using a domain user account. While I was able to connect to the Primary replica when I give instance name but when DB fails over to secondary and I try to connect using the same connection (without changing instance name) I am not able to connect. If I change the instance name to the other replica instance which is primary now, I could connect. Is this limitation of JTDS?

    And is there any workaround to achieve connection without instance name?

  10. alon says:

    my problem was the instance name which is the “server”+”\instance name”.
    finally see this page and see the host divided from instance

  11. Doug says:

    I am trying to connect to a local version of MS SQLServer Express 2016. I am getting the following error:

    [08S01] This driver is not configured for integrated authentication.

    I have tried changing the intergratedSecurity property and the authentication property within DataGrip. Is there something else I should try in SQL Server Configuration Manager?

  12. Saad M says:

    I am having a lot of difficulty connecting to my Azure DB-Server. It keeps saying open up the port to allow connections but port 1433 is receiving connections. I am using the jtds driver on my MacBook (OS Sierra), but I get one of two messages:

    1: [08S01] I/O Error: Broken pipe (Write failed)[08S01]
    2: And a message saying to check firewall rules (which I have done)

    Im very frustrated. The only thing I am not sure about is the Instance name field when testing the connection.

  13. Jubair Saidi says:

    This no longer works with the latest update from DataGrip using the DB Browser. MS SQL is no longer an option (only Postgres, mysql, Oracle, and Sqlite are available).

  14. Alex Doan says:

    I can’t connect from DataGrip on MacOSX to MSSQL. I often have error with contain “[08S01] Broken pipe ClientConnectionId:2100e91d-a192-40ef-aceb-d0a56f00c0d5”. I use version 2017.2.2

  15. Mr.Antunes says:

    Followed this through but I still can’t connect to a remote mssql 2014 server. I’m receiving the message invalid user/pass. The credentials I’m using are the same that I use on mssqlManagementStudio, so I’m sure they work.

    Screenshot link: https://ibb.co/btBMiR

    Any idea?

  16. dev says:

    Can’t connect with Windows authentication from MacOS!
    It was possible before but on 2017.2.3 I can’t get it to work.

    Could you please provide detailed information on how to login from MacOS with Windows Auth?

  17. Ryan Lindsey says:

    I’m trying to connect(jtds) to a SQL Server 2016 AlwaysOn readonly replica. I’m getting the following error :

    “[S1000][978] The target database (‘XXXXXX’) is in an availability group and is currently accessible for connections when the application intent is set to read only.”

    I’ve tried setting ApplicationIntent=ReadOnly in the connection url as well as in the advanced settings.

    I have no problem connecting to the primary sql server. Only the read replica.

  18. Kevin says:

    I am seeing an issue that is not shown here. When trying to connect to SQL Server on Mac OSX, i’m getting the following error:

    java.lang.ClassNotFoundException: jcifs.Config

    I cannot find this file on my Mac OSX – is there a file that’s missing or required framework? I know this lib is used to connect to SMBA servers so is that something I need to have pre-installed or downloading the missing drivers should have installed any missing dependencies?

  19. Vasilii says:

    @Kevin
    Could you email full IDE logs to vasily.chernov@jetbrains.com ? Also, specify your MS SQL Server version.

  20. Rodney says:

    Why is it every single time I create a new connection to MS SQL Server, I HAVE to specify the schema’s and the databases I want to see over in the “databases” pane?

    In SSMS if I connect to the instance with user AngelBob, I see everything that I have access to, by default, I don’t need to “turn it on”.
    Using Datagrip, I am forced to go into the connection properties, go to the schema tab and select all schemas of objects that I wish to see, including my own default schema, this makes the work flow and setup so cumbersome and slow.

    I’ve tried both driver options.

  21. Jason Li says:

    connection to SQL database tests successfully but in the top left database brower pane, i could not see database tables. it only shows one line saying “jdbc:sqlserver://10.10.177.133\SQL03,4444;database=master ….

    do I miss anything?

  22. Jason Li says:

    never mind. i figured it out. Thanks.

  23. Akbar N says:

    Hi,
    I have put the all the needed details for the connecting through windows authentication but still i’m getting the following error. Could you confirm what might be the reason for this?

    The same login is working on my windows laptop with SQL Server 2017.

    Error:
    Connection to DBCCCAAA is failed
    [28000][18452] Login failed. The login is from an untrusted domain and cannot be used with Window authentication.

  24. Anon says:

    Hi,

    Thanks for this post. I’m using the jTds driver on a Mac to connect to a server. I set DOMAIN to the relevant domain name, and USENTLMV2 is set to true, but when I tried to connect, I got: [08S01] I/O Error: DB server closed connection.

    I looked at the other Advanced options and on suspicion, enabled SSL, and was finally able to connect. (setting to request and require both worked, I’m guessing request would be safest to cover all scenarios). Anyway, posting here in case anyone else would find it useful

Leave a Reply to Carlos Cancel reply

Your email address will not be published. Required fields are marked *