Database Connection from RedHat Linux CentOS 8 RStudio Server to MS SQL Server Named Specific Instance

[Swamy]

Hi RStudio Community,

I am trying to establish connection to MS SQL server Database Specific Names Instance from RedHat Linux CentOs 8 RStudio Server using Windows Authentication. The issue I am facing here is Unless I Have Access to Default Instance, I Am Unable to Connect to Specific Named Instance.

**We are using Kerberos Token and AD System to use Windows Authentication"

Example:
MS SQL Server: 111.111.111.111
Default Instance: DefaultInstance
Named Instance: NamedInstance

If I have Access on 111.111.111.111\DefaultInstance and 111.111.111.111\NamedInstance then, below code works

DBI::dbConnect(odbc::odbc(),
                           driver = "ODBC Driver 17 for SQL Server",
                           server = "11.111.111.111\NamedInstance",
                           port = "1433"
                           trusted_connection = "yes")

If the Access to 111.111.111.111\DefaultInstance is revoked, the above code throws error nanodbc/nanodbc.cpp:983: 00000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user XXXXXX. [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute

Packages:
odbc 1.2.2
DBI 1.1.0
R Version 3.6.0

Is that a limitation to have access on default instance to access named instance on the same server? If not is there any other argument that I need to pass to connect directly to named instance even when I don't have access on default instance?

Any help is really appreciated. Thank you in advance.

[rstub]

Can you try leaving out port = "1433"? Most likely, the default instance is listening on that port.

[Swamy]

Thank you @rstub for helping with this. I did try leaving out the port and see below error.

Error: nanodbc/nanodbc.cpp:983: 00000: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: Server not found in Kerberos database [Microsoft][ODBC Driver 17 for SQL Server]Cannot generate SSPI context

May be useful link: https://support.microsoft.com/en-ca/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message

I did forward this to our DCOPS Team who configured Kerberos for me. Hope he will be able to understand the issue. I will keep you updated on this if we find the issue and solve it.

Want to keep this conversation as a document for future reference to the RStudio Community if possible.

Thank You
Hemanth

[Swamy]

Hi @rstub, sorry to get back to you regarding this issue. My DCOPS team member has provide a long message and In short he wants to understand a way to use UDP Port 1434 instead of TCP Port 1433 as our MS SQL Instance is listening to UDP Port 1434 it seems.

Based on his understanding and research he replied as follows:
Error: nanodbc/nanodbc.cpp:983: 00000: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: Server not found in Kerberos database [Microsoft][ODBC Driver 17 for SQL Server]Cannot generate SSPI context means when SSPI uses Kerberos authentication to delegate over TCP protocol and not able to complete operations.

Kerberos authentication uses an identifier named "Service Principal Name" (SPN). An SPN for SQL Server is composed of the following elements:

ServiceClass: This identifies the general class of service. This is always MSSQLSvc for SQL Server.

Host: This is the fully qualified domain name DNS of the computer that is running SQL Server.

Port: This is the port number that the service is listening on.

For example, a typical SPN for a computer that is running SQL Server is as follows:

MSSQLSvc/SQLSERVER1.northamerica.corp.mycompany.com:1433

So if the domain name OR the port number is not matching OR resolving, it throws SSPI error and in our case, service listening on UDP protocol and on port 1434 and not on the default port 1433 which uses TCP protocol. So need to specify UDP protocol type with port 1434 in the db connection string on R Side. IF we are able to successfully specify UDP protocol with 1434 port number, it will connect for sure.

[rstub]

From my understanding the communication to port 1434 via UDP should happen automatically when a named instance is involved, c.f. https://www.mssqltips.com/sqlservertip/2661/how-to-connect-to-a-sql-server-named-instance/. Have you checked the documentation of your ODBC driver for for how to connect with a named instance? Leaving out the port would be the correct thing to do in conjunction with our pro drivers, which you can use together with any of our pro products.

Alternatively, you could specify the port at which this particular named instance is listening. However, it is my understanding that these ports need not be stable.