Connecting DataGrip to MS SQL Server

Posted on by Maksim Sobolevskiy

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.

Configure the SQL Server Configuration Manager

To open SQL Server Configuration Manager, open the Search dialog on Windows and type SQLServerManager14.msc (for Microsoft SQL Server 2017). Double-click the found result. If you use other versions of Microsoft SQL Server, change the second digit before .msc:

  • SQLServerManager13.msc for SQL Server 2016

  • SQLServerManager12.msc for SQL Server 2014

  • SQLServerManager11.msc for SQL Server 2012

db_tutorial_open_sql_server_configuration_manager

Run the SQL Server Browser

SQL Server Browser listens for incoming requests and provides information about Microsoft SQL Server instances on the computer. For more information about SQL Server Browser, see SQL Server Browser in the Microsoft documentation.

If the SQL Server Browser menu items are disabled, try to enable the SQL Server Agent service.

  1. In the SQL Server Configuration Manager, click SQL Server Services.

  2. In the right pane that lists server services, right-click SQL Server Browser and select Start.

db_tutorial_run_sql_server_browser

Enable SQL Server Agent

If the SQL Server Browser is running, you can skip this procedure.

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks. For more information about these services, see SQL Server Agent in the official Microsoft documentation. Ensure that SQL Server Agent is running and starts automatically. After the change, a restart might be required.

  1. Navigate to Control Panel | Administrative Tools | Services.

  2. In the Services window, right-click SQL Server Agent(<server_name>) and select Properties. For this tutorial, <server_name> is MSSQLSERVER.

  3. From the Startup type list, select Automatic and click Start.

db_tutorial_run_sql_server_agent

Enable the TCP/IP connection

  1. In the SQL Server Configuration Manager, expand SQL Server Configuration and click Protocols for MSSQLSERVER, where MSSQLSERVER is a name of the Microsoft SQL Server instance.

  2. In the list of protocol names, right-click TCP/IP and select Properties.

  3. On the Protocol tab, from the Enabled list, select Yes.

  4. On the IP Addresses tab, find the interface that your workstation uses and see the connection port. By default, the port is 1433. Verify that other running applications do not use the same port.

db_tutorial_enable_tcp_ip

If you changed any settings, restart the server. For most situations, the restart resolves connection problems.

Connect to Microsoft SQL Server with DataGrip

We’ll describe configuration of DataGrip on WindowsmacOS, and Linux. Note that the Use Windows domain authentication checkbox is available only on Windows. To configure Windows domain authentication on macOS and Linux, see Connect by using Windows domain authentication.

Connect by using SQL Server authentication

  1. Navigate to File | Data Sources

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Microsoft SQL Server.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In HostInstance, and Port fields, specify your connection details.

  5. Clear the Use Windows domain authentication checkbox.

  6. In User and Password fields, specify your credentials.

  7. To ensure that the connection to the data source is successful, click Test Connection.

db_tutorial_connect_by_using_sql_server_authentication

Connect by using single sign-on for Microsoft SQL Server

If you run DataGrip on Windows in the same domain as the Microsoft SQL Server database, you can use the Single-Sign On (SSO).

  1. Navigate to File | Data Sources.

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Microsoft SQL Server.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. From the Authentication list, select Windows credentials.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Connect by using SQL Server authentication

Connect by using Windows domain authentication

Windows

  1. Navigate to File | Data Sources

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Microsoft SQL Server.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In HostInstance, and Port fields, specify your connection details.

  5. Ensure that the Use Windows domain authentication checkbox is selected.

  6. To ensure that the connection to the data source is successful, click Test Connection.

db_tutorial_connect_by_using_windows_domain_authentication

macOS and Linux

  1. Navigate to File | Data Sources 

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Microsoft SQL Server.

  3. From the Driver list, select Microsoft SQL Server (jTds).

  4. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  5. In HostInstance, and Port fields, specify your connection details.

  6. From the Authentication list, select Domain credentials.

  7. In the Domain field, specify the domain (for example, DEVELOPMENT).

  8. In User and Password fields, specify your domain credentials. In the User field, type your domain user without the domain prefix (for example, John.Smith instead of DOMAIN\John.Smith).

    Alternatively, on the General tab, specify the connection string. Consider the following example of a full connection string:

    jdbc:jtds:sqlserver://UNIT-670:1433;domain=DEVELOPMENT;instance=MSSQLSERVER;databaseName=guest;
    Copied!
  9. To ensure that the connection to the data source is successful, click Test Connection.

Connect by using SQL Server authentication

Choose databases and schemas

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:

Screenshot 2019-09-09 16.31.18

Or click on the n of m  text area near the data source name in the database explorer.

Screenshot 2019-09-09 16.31.38

All selected databases and schemas will appear in the database view.

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

Comments below can no longer be edited.

77 Responses to Connecting DataGrip to MS SQL Server

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

    July 20, 2016

    […] those who had problems connecting to SQL Server, we published a small tutorial covering the most common […]

  2. Matt says:

    December 7, 2016

    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

    • Maksim Sobolevskiy says:

      January 20, 2017

      Thank you! I’ve added it to the post.

  3. Al H. says:

    December 19, 2016

    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.

    • Maksim Sobolevskiy says:

      January 20, 2017

      You mean server AND domain or only domain name?

      • Sam A. says:

        February 7, 2018

        I had a similar experience as Al. The value of the DOMAIN setting should be ONLY the domain name, not the the server name.

        • Maksim Sobolevskiy says:

          February 8, 2018

          It was a mistake of the tutorial Of course, there should be a domain name.

  4. Bill says:

    January 6, 2017

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

  5. Toby says:

    February 21, 2017

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

    • Maksim Sobolevskiy says:

      February 21, 2017

      DataGrip supports SQL Server 2016. What problem did you face?

  6. Cain says:

    May 27, 2017

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

    • Maksim Sobolevskiy says:

      May 29, 2017

      Please, send a screenshot to maxim.sobolevskiy@jetbrains.com

      • Dave M says:

        June 16, 2017

        I have the same issue.

        • Maksim Sobolevskiy says:

          June 19, 2017

          So I also wait for a screenshot from you.

        • Maksim Sobolevskiy says:

          July 31, 2017

          This check-box is not supposed in OSX. To use Windows Authentification from MacOSX, use jtds driver as it’s described in the blog-post. If it doesn’t work (as I got from your message), please, describe it here.

          • Swade says:

            November 30, 2017

            The blog post says to click on the Windows Authentication checkbox and the post itself is prefaced by connecting from OSX. Is this not actually for that?

          • Maksim Sobolevskiy says:

            December 4, 2017

            Swade, the screenshot shows that this check-box works on Windows.

    • Carlos says:

      July 4, 2017

      Same problem. No Windows Authentication option. Running DataGrip 2017.1.

      • Maksim Sobolevskiy says:

        July 17, 2017

        Please, send a screenshot to maxim.sobolevskiy@jetbrains.com

        • Mohsin Patel says:

          July 18, 2017

          Same problem – on Datagrip 2017.1.5 and no check box for Windows Auth for the Microsoft SQL Driver

          • Maksim Sobolevskiy says:

            July 25, 2017

            A screenshot, please! 🙂

          • Maksim Sobolevskiy says:

            July 31, 2017

            This check-box is not supposed in OSX. To use Windows Authentification from MacOSX, use jtds driver as it’s described in the blog-post. If it doesn’t work (as I got from your message), please, describe it here.

  7. Mahesh says:

    June 5, 2017

    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:

    June 8, 2017

    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:

    July 11, 2017

    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:

    July 22, 2017

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

    • Maksim Sobolevskiy says:

      July 25, 2017

      So, everything is fine now?

  11. Doug says:

    August 25, 2017

    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?

    • Maksim Sobolevskiy says:

      August 30, 2017

      What driver do you use? Microsoft?

      • Doug Rimmer says:

        September 1, 2017

        Yes, Microsoft JDBC Driver 6.2 for SQL Server.

        • Maksim Sobolevskiy says:

          September 1, 2017

          Is it the same with jtds driver?

  12. Saad M says:

    September 27, 2017

    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:

    September 27, 2017

    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).

    • Maksim Sobolevskiy says:

      September 29, 2017

      What is DataGrip using DB Browser?

  14. Alex Doan says:

    October 12, 2017

    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:

    November 16, 2017

    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:

    November 30, 2017

    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?

    • Maksim Sobolevskiy says:

      December 4, 2017

      But before it worked with DOMAIN field, right?

  17. Ryan Lindsey says:

    January 9, 2018

    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.

    • Ryan Lindsey says:

      January 9, 2018

      Looks like it’s currently unsupported in jtds.

      https://sourceforge.net/p/jtds/bugs/758/

      Considering I’m on a mac and using windows auth, is there an alternative? I can’t seem to get the Microsoft java driver working with windows auth in Datagrip on a mac.

  18. Kevin says:

    April 16, 2018

    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:

    April 27, 2018

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

  20. Rodney says:

    August 23, 2018

    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.

    • Maksim Sobolevskiy says:

      December 19, 2018

      It happens because when we load schema to the tree, we load EVERYTHING from it to the local storage to provide many IDE features then.

      Please, follow the issue: https://youtrack.jetbrains.com/issue/DBE-6762

  21. Jason Li says:

    April 5, 2019

    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?

    • jerrytim says:

      March 1, 2020

      I had a very similar issue like yours: test successfully but could not see any tables. I was using jTds driver, I resolved it by specifying the full server address instead of server name in the Host field, and specifying the domain in the Advanced tab. The domain is full server address without server name.
      Your issue might be sth different but hope it helps.

  22. Jason Li says:

    April 5, 2019

    never mind. i figured it out. Thanks.

  23. Akbar N says:

    May 13, 2019

    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.

    • Maksim Sobolevskiy says:

      May 14, 2019

      Are you on Windows?

      • Travis Odom says:

        July 24, 2019

        I’m having this issue in Linux.

        • Vasilii says:

          July 25, 2019

          Could you e-mail me vasily.chernov@jetbrains.com your IDE logs along with screenshots of your data source settings?

        • Maksim Sobolevskiy says:

          July 29, 2019

          Also please check if you entered the domain

  24. Anon says:

    July 11, 2019

    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

  25. Sergey says:

    October 7, 2019

    Is it possible to set up connection to MSSQL such way that it will include only databases with some prefix?

  26. Rajeev says:

    January 7, 2020

    I can connect Sql Server through my Java application using the driver ‘com.microsoft.sqlserver:mssql-jdbc:6.2.2.jre8’.
    But DataGrip not working. Why we need all the above steps?

    • Maksim Sobolevskiy says:

      January 13, 2020

      What do you mean by ‘not working’?

  27. mrsk says:

    February 26, 2020

    I can’t see SQL Server Browser in configuration manager

    • Maksim Sobolevskiy says:

      February 27, 2020

      Please show a screenshot.

      • mrsk says:

        February 29, 2020

        https://imgur.com/HM2HiYN

        • Maksim Sobolevskiy says:

          March 3, 2020

          Seems that Обозреватель is Browser

  28. jerrytim says:

    March 1, 2020

    Apparently DataGrip should have done a much better documentation especially for Mac users…
    Using SQL Server authentication I was never able to connect to SQL Server, the default Microsoft SQL Server driver seems not working.
    Using jTds server, one should specify the domain in the Advanced tab, if you only know the server and instance, try to ping it in your terminal/iterm by doing: ping -a server. You should see it resolves to PING server.domain like (server.xxx.com). The domain part (xxx.com) is what you need to put in the domain field.
    Hope this help others.

  29. Dan Clarke says:

    April 17, 2020

    Why are all these steps required? SSMS doesn’t require these changes, neither do other clients, eg. LINQPad. It feels weird changing all these settings just to use DataGrip.

  30. Mike says:

    June 19, 2020

    First time user…when I add a new MS SQL Server (2005) using the same details I would in SSMS with Windows Auth I get a error with just . . Nothing else. What does this mean? I have tried all versions of the driver and cannot find anything in online help. I have no issues connecting with SSMS.