B4J Question JDBC SQL Server connection using Shared Memory Protocol

Chris2

Active Member
Licensed User
Longtime User
Does anyone have any experience with or know if it's possible to connect via JDBC to a local (same machine) installation of SQL Server using the Shared Memory Protocol?
I have a jRDC2 based app which I have connecting to the database fine using a TCP/IP connection, but I've been asked if it can use Shared memory Protocol instead.

I've disabled TCP/IP protocol on the SQL Server (Express) that I have installed for testing and based on what I've read in:

and a few other places, I've been trying to connect to the database just with a simple test app:
B4X:
'Non-UI application (console / server application)
#Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True
#End Region

Sub Process_Globals
    Private sql As SQL
End Sub

'DB drivers.
'MSSQL
#AdditionalJar: mssql-jdbc-12.6.2.jre11

Sub AppStart (Args() As String)
    
    Dim drvClass As String = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    
'    Dim jdbcURL As String = "jdbc:sqlserver://SERVERNAME:PORTNUM;instanceName=sqlexpress;databaseName=DBNAME;encrypt=true;trustServerCertificate=True"

'    Dim jdbcURL As String = "jdbc://lpc:localhost;instanceName=sqlexpress;databaseName=DBNAME;"
'    Dim jdbcURL As String = "jdbc:sqlserver://lpc:(local);instanceName=sqlexpress;databaseName=DBNAME;"
'    Dim jdbcURL As String = "jdbc:sqlserver:lpc:(local);instanceName=sqlexpress;databaseName=DBNAME;"
'    Dim jdbcURL As String = "jdbc:sqlserver://(local);instanceName=sqlexpress;databaseName=DBNAME;"
'    Dim jdbcURL As String = "jdbc:sqlserver://(local)\sqlexpress;databaseName=DBNAME;"
    Dim jdbcURL As String = "jdbc:sqlserver://localhost\sqlexpress;databaseName=DBNAME;"
'    sql.Initialize2(drvClass, jdbcURL, usr, pw)
    sql.Initialize(drvClass, jdbcURL)

    Log("Number of rows = " & sql.ExecQuerySingleResult("SELECT count(*) FROM TABLE1"))
    sql.Close
End Sub

Before disabling TCP/IP connections on the SQL Server I checked using the first commented jdbcURL and the sql.Initialize2 line that the query runs OK, connecting via TCP/IP. All OK there.
But when I disable TCP/IP and try to form the correct jdbcURL for a Shared Memory connection, I get one of various errors:
B4X:
java.sql.SQLException: No suitable driver found for jdbc:sqlserver:lpc:(local);instanceName=sqlexpress;databaseName=DBNAME;

com.microsoft.sqlserver.jdbc.SQLServerException: The port number (local) is not valid.

com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host (local), named instance sqlexpress failed. Error: "java.net.UnknownHostException: (local)". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.

I have found a few posts stating that the JDBC driver does not support the Shared Memory Protocol, but these were from 10+ years ago, so I'm hoping they're out of date.

Does anyone know how/if we can connect to a local SQL Server database with the Shared Memory Protocol?

Many thanks!
 

Jmu5667

Well-Known Member
Licensed User
Longtime User
I put this into Copilot:

Shared Memory Protocol

The Shared Memory Protocol is a method used by SQL Server to allow clients to connect to a SQL Server instance on the same computer. It's the simplest protocol to use and has no configurable settings2. Here are some key points:
  • Usage: It's primarily used for troubleshooting when other protocols (like TCP/IP or Named Pipes) are suspected to be misconfigured.
  • Limitations: Clients using the shared memory protocol can only connect to a SQL Server instance running on the same computer.
  • Configuration: You can enable or disable the shared memory protocol using the SQL Server Configuration Manager.

Why do you want to use it ?
 
Upvote 0

tchart

Well-Known Member
Licensed User
Longtime User
20+ years of using SQL Server and not once used Shared Memory.

Anyway, a couple of things;

1. Your post mentions sqlexpress, by default tcp/ip is disabled (only shared memeory is enabled by default). So perhaps the customer doesnt realise they need to enable tcp/ip?
2. If they are concerned about security just deny sql server on the firewall, local processes will still be able to connect
3. Workarounds appear to be to set up an ODBC datasource and then use JDBC to connect to that
 
Upvote 0

Chris2

Active Member
Licensed User
Longtime User
Thanks for the replies @tchart and @Jmu5667.

I'm not looking for other options though. We (me & the customer) are aware that TCP/IP is the more normal route and is probably what will end up being used.

I just thought I'd try to see if I could get a Shared Memory Protocol connection to work, and when I couldn't thought I'd ask the question.
If the answer is that the JDBC driver doesn't support it or that it needs a complex workaround, then that's fine, we'll use TCP/IP.
 
Last edited:
Upvote 0

Chris2

Active Member
Licensed User
Longtime User
I think you are being misled
I don't think so. My statement "I guess it would allow TCP/IP connections to be disabled, increasing security." was just as it says, my guess. If it's nonsense, then the nonsense came from me :).

My only question here was asking if we can somehow use the Shared Memory Protocol to connect to an SQL Server via JDBC and jSQL.
 
Upvote 0

tchart

Well-Known Member
Licensed User
Longtime User
I don't think so. My statement "I guess it would allow TCP/IP connections to be disabled, increasing security." was just as it says, my guess. If it's nonsense, then the nonsense came from me :).

My only question here was asking if we can somehow use the Shared Memory Protocol to connect to an SQL Server via JDBC and jSQL.
I believe the answer is no. The JDBC driver doesn’t appear to support this.
 
Upvote 0
Top