MBS MSSQL Connect problem - VPN

Databases
1

Im using the 2022r3 build on Win. I am successfully connecting SSMS over the VPN and Im using the exact same creds for the MBS connection but I get an error, “Login failed for user ‘sean’.” My code is like the below except slightly different creds but Ive triple checked they do match what works on ssms. I get the exception and error at the “if db.Connect then”

dim db as new SQLDatabaseMBS

db.Option("UseAPI") = "OLEDB"
db.Option("SQLNCLI.LIBS") = "sqlsrv32.dll" // Library included in Windows Vista and newer

try
  
  db.DatabaseName = "SQLServer:"+"192.168.100.25\TestDB"
  db.UserName = "sean" // tfUserName.text
  db.Password = "password" //tfPassword.text
  db.RaiseExceptions = true // if you like to get exceptions instead of checking error property
  db.Scrollable = false // disabling scrolling cursors is much faster for Microsoft SQL Server...
  
  if db.Connect then
2

Check the setting for the user on the MS SQL Server.
it may not be allowed to login with your IP.

3

Im not seeing anywhere in the user that would indicate that. Im obviously not well versed at sql server user mgmt. I am able to use the server with server mgmt studio with the same info over the vpn…

4

Hi Christian, maybe in the am you could elaborate a little on how I migh tfix that? I cant find any setting for my user ip address anywhere. Ive been looking in the user props and in server config manager, I cant find anything that looks like user ip settings at all…thanks!!

5

I think this should be:
db.DatabaseName = "SQLServer:" + 192.168.100.25@TestDB"

6

'Than you so much Wayne, I had high hopes but its a slightly different error msg:

HYT00 Login timeout expired
08001 A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server [2].

that i think leads to concluse the other pathing is better but still a hitch in the incoming ip or such? Just to asuage the thought, I do only use a single string so theres no concat error missing the quote between +

7

I googled “08001” and found this:

SQL Server Connection failed : SQLState 08001 - Let’s fix it!! (bobcares.com)

This page talks about named pipes. Named pipes is one of the protocols used between the server and the client. That suggests that’s the protocol the client - your Xojo app" tried to use, but that failed because Named pipes are not enabled on the server.

Each protocol you wish yo use have to be enabled has to be enabled on the server, can’t tell if it’s at the database level or server level. It is also possible that the client, at the OS level, needs to be properly set.

I will try to see if I have something on my development computer.

8

Gilles, thanks so much for looking and commenting. I am of the thought the pipes business just cropped up when I tried Wayne’s twist on the server string. Pipes are not enabled on this server but the main sticking point im haveing that I can connect quite perfectly with ssms on this same client using the exct same creds, yet cant via xojo with natural or mbs methods. Im sure I have something wrong but cant find it…

9

You may need to specify the SQL instance as part of the connection e.g.

db.DatabaseName = "SQLServer:192.168.100.25\SQLExpress@TestDb"

10

SQL Server does not allow network connections by default. There could be a setting in SQL or Windows Firewall that’s blocking your connection.

Also could be a problem with the local SQL driver. I don’t have any experience with MBS plugins, but I’m curious about:

db.Option("SQLNCLI.LIBS") = "sqlsrv32.dll"

Have you tried using the Xojo built-in connection? Maybe this will work, or will provide a different error that will help. I haven’t tried this with a recent version of Xojo as the app has been stable for a while. Here’s how I connect. Note I’m using the Xojo ODBCDatabase which I find works better than MSSQLServerDatabase. Also I’m using the trusted connection (instead of supplying a username and password) which works great in a Windows domain assuming you have permissions set correctly on the SQL Server and SQL Database.

Public Property dbDocs As ODBCDatabase

Public Const DatabaseConnectionDocs as String = Driver={SQL Server};Server=SQLServerName\SQLInstanceName;Database=DatabaseName;TrustedConnection=Yes;
dbDocs = New ODBCDatabase
dbDocs.DataSource = DatabaseConnectionDocs

If dbDocs.Connect Then
  ' all good
11

I haven’t had either the need or opportunity to connect to a Sql Server instance with Xojo (I’ve been using Postgres), but I’ve used Sql Server in my professional life since v 7.0 in the 1990s and the trusted connection method has literally never caused me problems. Of course the owner of the domain that the server is in has to grant you Windows credentials, and probably use up a Windows license seat. But it’s definitely best. Second best is Sql Server authentication which only requires a Sql Server id/password and that mixed authentication is enabled. We still use that a lot to give individual processes / services their own login identity so it’s easier to see where DB traffic is coming from.

12

@Wayne_Golding it was a form of this answer that ultimately worked with mbs ODBC and the native driver. Heres the connection info that finally worked for me:

Be sure the VPN is connected and working (I did this via ssms)
con.Option("UseAPI") = "ODBC"
"SQLServer:" + "RDServername@DBName"
username
password

Thanks very much everyone for the thoughts and help. I am going to read up on the trusted connection bits too!