Datagrip连接SQLServer Connecting DataGrip to MS SQL Server

忘是亡心i 2022-02-24 13:38 1198阅读 0赞

# Connecting DataGrip to MS SQL Server #

Posted on [June 21, 2016][June 21_ 2016] 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.

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

[![SQLServer Browser][]][SQLServer Browser]

*TCP/IP* connections need to be enabled:

[![tcp][]][tcp]

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

[![Port][]][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][]][NewDataSource]

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

[![Download drivers][]][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][]][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][]][PortConflict]

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

[![Windows auth][]][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][]][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][]][SchemasTab]

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

[![MoreSChemas][]][MoreSChemas]

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

[![Databases][]][Databases]

And here’s how it looks in DataGrip:

[![ManyDBSCurrent][]][ManyDBSCurrent]

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

Your DataGrip Team

*The Drive to Develop*

[June 21_ 2016]: https://blog.jetbrains.com/datagrip/2016/06/21/connecting-datagrip-to-ms-sql-server/
[Maksim Sobolevskiy]: https://blog.jetbrains.com/datagrip/author/moscas/
[SQL Server]: https://www.microsoft.com/en/server-cloud/products/sql-server/
[SQL Server Configuration Manager]: https://msdn.microsoft.com/en-us/library/ms174212.aspx
[SQLServer Browser]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/SQLServer-Browser.png
[tcp]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/tcp.png
[Port]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/Port.png
[This tutorial]: http://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/
[NewDataSource]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/NewDataSource.png
[Download drivers]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/Download-drivers.png
[Connect SSMS Compare]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/Connect-SSMS-Compare.png
[PortConflict]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/PortConflict.png
[Windows auth]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/Windows-auth.png
[AdvancedForWindows]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/AdvancedForWindows.png
[SchemasTab]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/SchemasTab.png
[MoreSChemas]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/MoreSChemas.png
[Databases]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/Databases.png
[ManyDBSCurrent]: https://d3nmt5vlzunoa1.cloudfront.net/datagrip/files/2016/06/ManyDBSCurrent.png
[tweet _ us]: https://twitter.com/0xdbe
[youtrack]: https://youtrack.jetbrains.com/issues/DBE