Connect to SQLServer (named instance) from Mac (MBS)

Add-Ons
1

Hello,
In an application I am connecting to MS SQL Server from Mac using the libtdsodbc.dylib.
If the instance of SQL Server is not named (e.g. 192.168.1.163) it connects with no problem.
However if the instance is named (192.168.1.163\SQLEXPRESS) then it doesn’t connect with the following error:

08S01 [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist08001 [FreeTDS][SQL Server]Unable to connect to data source
08S01 [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist

Should I specify the instance name in some other way? Or is there something else wrong?

2

Did you try it with 192.168.1.163@SQLEXPRESS ?

Or with a Connect String like:

"Driver={FREETDS};Server=192.168.1.163;UId=LOGIN_NAME;PWD=PASSWORD;Database=SQLEXPRESS;TDS_VERSION=7.2;Port=1433"

3

Forget it. I confused it with the Databasename… but i leave the info online, for maybe it will help you later…

4

Hello Sascha,
Thanks for the effort anyway!
Cheers…

5

Did you try Instance=name in the connection string?

6

Hi Christian,
Do you mean something like

“Driver={FREETDS};Server=192.168.1.163;Instance=SQLEXPRESS;UId=LOGIN_NAME;PWD=PASSWORD;Database=His;TDS_VERSION=7.2;Port=1433”

7

yes, does it work?

or did you try “192.168.1.163\SQLEXPRESS” for server?

8

I tried “192.168.1.163\SQLEXPRESS” for server and it throws an exception.
I will try the instance property and see if it works.

I will let you know…

9

Christian it doesn’t seem to work. The code is:

    dim cs as string = "DRIVER={FREETDS};Server=192.168.1.163\\sqlexpress;UId=sa;PWD=55d4xs223"+_
    ";Database=his;TDS_VERSION=7.2;Port=1433"
    db.DatabaseName = cs
    db.SetFileOption db.kOptionLibraryODBC, libtdsodbc
    db.Option("UseAPI") = "ODBC"
    db.DatabaseName = "ODBC:"+cs
    db.UserName = ""
    db.Password = ""
    If Not(db.Connect()) Then
      MsgBox db.ErrorMessage
      'Break
      Return
    End If
    db.Scrollable = false

Or with the instance:

    dim cs as string = "DRIVER={FREETDS};Server=192.168.1.163;Instance=sqlexpress;UId=sa;PWD=55d4xs223"+_
    ";Database=his;TDS_VERSION=7.2;Port=1433"
    db.DatabaseName = cs
    db.SetFileOption db.kOptionLibraryODBC, libtdsodbc
    db.Option("UseAPI") = "ODBC"
    db.DatabaseName = "ODBC:"+cs
    db.UserName = ""
    db.Password = ""
    If Not(db.Connect()) Then
      MsgBox db.ErrorMessage
      'Break
      Return
    End If
    db.Scrollable = false

On a different server that has no named instance exactly the same code (1st version with no Instance property) works!

Any ideas?

10

I don’t use instance or ‘…\SQLEXPRESS’ when connecting to MSSQL from a Mac and it works fine. If the database doesn’t exist yet (because I want to create it) then I leave '+ “;Database=” + myDatabaseName ’ out.

Where myHost = “192.168.1.163”

11

Thanks David,

I have connected successfully to SQLServers with no instance name (e.g. 192.168.1.163).
The problem is if the instance is named (usually the instance name is sqlexpress if they use sqlexpress). In that case, I don’t seem to be able to connect.

However, I think that there should not be a problem, probably something needs to be passed to FREETDS differently ?!

12

Looking at the FreeTDS help pages, it should work with “Server\instance”. Not sure why this fails.

13

I seem to have found a solution!
The solution concerns the port. So if the instance is named, the default port for SQL Server service is not set and what I needed to do is:

  1. Open “Sql Server Configuration Manager”
  2. Go to “SQL Server Network Configuration > Protocols for SERVER”
  3. Right click “TCP/IP”, select “Properties”, go to the “IP Addresses” tab
  4. Under “IPAll” set “TCP Port” to the desired port number (IPAII was the last in my setup)
  5. Click OK, and restart the SQL Server service.

Then I could connect, either as 192.168.1.163\sqlexpress or as 192.168.1.163 (without the instance name).
It seems that the instance name is ignored, however, if the port is set correctly it connects!

Now there should be another way maybe, but this is a working solution (workaround).

14

normally the lib would connect to server, ask for port of the instance and than connect to that port.
So the instance name is just used to find the port.

15

Ok Christian, you are right.
It seems to work with no problem with the Server=192.168.1.163\sqlexpress if you don’t include the port parameter

DRIVER={FREETDS};Server=192.168.1.163\sqlexpress;UId=sa;PWD=55d4xs223;Database=his;TDS_VERSION=7.2

It works