SQL Express with MBS SQL Plugin

Databases
1

I’ve been successfully connecting to MS SQL Server in my own network but I have a customer who cannot connect in theirs.
They are using SQL Express.

This is the code I’m using to configure my connection (which works for me). Do I have to modify this for Express?
Anything else jump to mind about why this might not work?
Is there any prerequisite libraries that they may need to have installed on a client machine that may not be there? (I am not a Windows guy!)

cs = host + “@”+DatabaseName
con.UserName = User
con.Password = pass
con.Option(“OLEDBProvider”) = “SQLNCLI”
con.DatabaseName = “SQLServer:”+cs

2

Is SQL Server Express configured to allow TCP/IP connections?
Have they included the SQL Server Instance name in the connection information (host\instance)?

See also this forum thread.

3

Default mode for SQL Plugin is to use ODBC on Windows.
So if you like to use OLEDBProvider, you may need to use con.Option("UseAPI") = "OLEDB" to request it.
See an example project like SQLDatabaseMBS Microsoft SQL Connect and query version.

Also server may need to have the port defined (1433) and TCP enabled, which is always a pain.

Connection String: A connection string like “[[<server_name>@][<database_name][;<driver_connection_option_list>]”
<server_name> - connects to a specified server. If it’s ommitted SQLAPI++ tries to connect to default local server instance.
<database_name> - connects to a database with the specified name. If it’s ommitted SQLAPI++ tries to connect to default database.
<driver_connection_option_list> - SQL Server Native ODBC driver specific option list.
To connect to a named instance of SQL Server use <server name\instance name> instead of <server_name> .
Since SQLNCLI (SQL Server 2005) also available protocol specific server name part of the connection string:
Shared memory: lpc:[\instancename]
TCP/IP: tcp:[<instancename>], or tcp:[<instancename>],
Named pipes: np:\<computer_name>\pipe<pipename> or np:\\pipe<pipename>
VIA: via: [\instancename],:

4

They did set it up to use TCP/IP connections.
I asked them to add the instance (that was missing) but it’s still not working for them.

For SQL Server it doesn’t seem to need the \instance part - does that sound right?

I’m asking them to install any other application that connects to SQL Express so that they can test - show that it is / is not my application. I don’t even know what to suggest they test with. Frustrating.

5

My guess is that it is a port number issue. Get them to try to Ping that port for a response.

6

To make your example work when connecting to my SQL Server I have to change this:

Windows7PC\SQLEXPRESS@test

to this:

my-IP-address@my-database-name, removing the SQLEXPRESS.

In code this assembles a connection string of:

SQLServer:192.168.4.46@master

When connecting to SQL Express do I still assemble the connection string with SQLServer: before the IP address? I’m assuming so.

The customer showed me the host address they used and it has \sqlexpress after the IP address, but on second look they have the name of the machine, not the IP address, so I’ll ask them to test with the IP address instead.

I’m also assuming these connection strings are not case sensitive?

I think I’m overthinking this. It must be in their server setup.

7

They can ping it, but that doesn’t test the port number. I’ll have them try with telnet. Thanks, David.

8

You are right, I meant a Port Scan.

1 Like
9

curl -v http://SOMEIP:1433

In Terminal simply and see if curl says it got a connection or it times out.

10

“SQLServer:” as prefix tells SQLDatabaseMBS class, that you like to use SQLServer client.

Behind that, you may specify whatever is given to SQL Server client library to connect. This may be via TCP/IP, via named pipe or VIA.
So using “\MyInstance@MyDatabase” may work for VIA and pick the port automatically.

1 Like
11

and in this string, how do I pass the port number?

12

IP,Port separated with comma usually.

13

that’s not behaving but I’ll test more.

Also adding the connection timeout to the connection string seems to be ignored.

14

Connection string in the following format:

[<server_name>@][<database_name>][;<driver_connection_option_list>]
  • <server_name> - connects to a specified server. If it’s omitted SQLAPI++ tries to connect to default local server instance
  • <database_name> - connects to a database with the specified name. If it’s omitted SQLAPI++ tries to connect to default database
  • <driver_connection_option_list> - SQL Server Native ODBC driver specific option list

To connect to a named instance of SQL Server use <server name\instance name> instead of <server_name>(use double back slash in C++ constants, for example "MyServer\\SQLEXPRESS@pubs").

Since SQLNCLI (SQL Server 2005) also available protocol specific server name part of the connection string:

  • lpc:<servername>[\instancename] - using shared memory
  • tcp:<servername>[\<instancename>],<port> or tcp:<IPAddress>[\<instancename>],<port> - using TCP/IP
  • np:\<computer_name>\pipe\<pipename> or np:\<IPAddress>\pipe\<pipename> - using named pipes
  • via:<servername>[\instancename],<nic_number>:<port> - using VIA

So you see it’s : with port or , with port. But since IPv6 has : in the IP, we switched to comma.

15

Hi Chris, I don’t know if you have found the solution to your problem, but if you haven’t…
I always use SQL Server Express with MBS Plugin: in the connection string you DON’T HAVE TO remove the instance name (SQLEXPRESS in your case).
So the connection string should be: “SQLServer:192.168.4.46\SQLEXPRESS@master”

Hope this can help you.

2 Likes
16

this also worked for me.
Thank you.

1 Like