DataGrip to MS SQL Server & Dynamic Ports

  • Brandie Tarvin

    SSC Guru

    Points: 172535

    Has anyone ever used DataGrip on a Mac to connect to SQL Server?

    We have a dev in India trying this and he can't connect. I'm 98% certain the issue is that he's got port 1433 in his connection string. We use dynamic ports, not the default. I'm trying to figure out how to set up a connection string for DataGrip that doesn't require a hard coded port number. Do I just leave the port number out and trust the Mac to figure it out? Or is there something in DataGrip that allows for dynamic port use?

    Or should I just tell them to use Azure Data Studio?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin

    SSC Guru

    Points: 172535

    I was finally able to catch our Indian dev near the end of his shift. It's not a port issue. Or if it is, removing the port still doesn't allow access. He sent me an error via Teams that seems to be a SQL error, but didn't tell me if it came from DataGrip or Azure Data Studio (which I recommended he install so we could verify if it was truly a windows login problem or a software client issue).

    System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'UserLogin'.

    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken) in /xplat/cfxfork/corefx/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlInternalConnectionTds.cs:line 400

    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) in /xplat/cfxfork/corefx/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlConnectionFactory.cs:line 136

    at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) in /xplat/cfxfork/corefx/src/Common/src/System/Data/ProviderBase/DbConnectionFactory.cs:line 96

    at System.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass40_0.<TryGetConnection>b__1(Task`1 _) in /xplat/cfxfork/corefx/src/System.Data.SqlClient/src/System/Data/ProviderBase/DbConnectionFactory.cs:line 86

    at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()

    at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

    --- End of stack trace from previous location where exception was thrown ---

    at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)

    --- End of stack trace from previous location where exception was thrown ---

    at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass28_0.<<OpenAsync>b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\ReliableSqlConnection.cs:line 303

    --- End of stack trace from previous location where exception was thrown ---

    at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 521

    ClientConnectionId:d7287cba-64fb-4705-bcf5-a3fbb450d54f

    Error Number:18456,State:1,Class:14

    Any ideas on this one? He seems convinced that he cannot connect to SQL Server from a Mac with a windows integrated login but there is no way we're creating a SQL Login (which works for a different database/different department they all log into using DataGrip -- And don't get me started on shared passwords!) for him.

    I would love options or thoughts on this one, please.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182349

    IIRC, 18456:1:X error should result in an entry in the SQL Error Log, have you checked if anything is there?

    😎

    Quick question, is this a named instance?

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88110

    Is the user account in SQL Server set as Windows or SQL account?  If using Windows Authentication - have you confirmed that Kerberos is configured correctly and that the user's machine is connected to the domain and able to use Kerberos?

    https://docs.microsoft.com/en-us/sql/azure-data-studio/enable-kerberos?view=sql-server-2017

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Brandie Tarvin

    SSC Guru

    Points: 172535

    SQL log seems to think he's logging in with a SQL Server login. I gave him instructions on how to set up DataGrip for windows authentication, but he says it's not working and did not give me any updated errors. Yes, this is a named instance. We don't use defaults right now.

    I'll check the Kerberos thing Monday morning when we're online for a meeting. I do notice the link Jeff added deals with Azure Data Studio. Would the same thing apply regardless of client software?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88110

    Brandie Tarvin wrote:

    SQL log seems to think he's logging in with a SQL Server login. I gave him instructions on how to set up DataGrip for windows authentication, but he says it's not working and did not give me any updated errors. Yes, this is a named instance. We don't use defaults right now.

    I'll check the Kerberos thing Monday morning when we're online for a meeting. I do notice the link Jeff added deals with Azure Data Studio. Would the same thing apply regardless of client software?

    Yes - the same thing will apply for any non-Windows systems attempting to connect using Windows Authentication.

    You stated it appears that the user is attempting to login with a SQL account, but you haven't stated whether or not his account is setup as Windows or SQL.  If the account is Windows and he is trying to login as a SQL account it will not work - the same if the account is a SQL account and he is attempting to login with Windows.

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Brandie Tarvin

    SSC Guru

    Points: 172535

    The login he's using is his own Windows account. See the end of the second post where I maybe not clearly mentioned it and indicated he wasn't going to get a SQL account for this.

    Thank you for the Kerberos clarification. I've emailed the information to him and we'll see this morning if it works.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply