Connecting DataGrip to MS SQL Server

UPDATED: September, 2019

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

  7. In the URL string, add the following parameters: ;domain=your_domain;instance=your_ms_sql_server_instance;databaseName=database_name;. Consider the following example of a full connection string: jdbc:jtds:sqlserver://UNIT-670:1433;domain=DEVELOPMENT;instance=MSSQLSERVER;databaseName=guest;.

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

db_tutorial_macos_linux_connect_by_using_windows_domain_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

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 Jubair Saidi Cancel reply

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