Connection to SQL server - Failed

Question

I have a test database created by the colleagues from IT department and I can also access / see it using MS SQL Server Management Studio 2016. However, I cannot establish a connection to it from within Mathematica notebook... I read the documentation but I'm not confident with databases in general, so maybe I make a basic mistake somewhere. What I do is:

Needs["DatabaseLink`"]
conn = OpenSQLConnection[]

That opens the Connection Tool GUI, where I define the new connection:

• define the name and description of database (test / test database);
• visibility (user level);
• type of database (Microsoft SQL Server jTDS);
• Connection: Hostname (wwe***pe..*.ds\MSSQL***); Port (1433); Database (empty); Attributes (empty); Username (****); Password (*******).

The hostname, username and password are the ones which I use (and worked) for connecting to database via MS SQL Server Management Studio. At this stage, when I push the "Test" button, I get after a few seconds the error message

Error: JDBC::error

On the next page, "Connection properties", I leave everything unchanged, than I click "Finish". Now, when I try

conn = OpenSQLConnection["test"] 

I get (almost obviously) the error

JDBC::error: Unknown server host name 'wwe**pe.**.***.ds\MSSQL***'. 
$Failed

It would be nice if somebody could guide me to solve this connection issue. Thanks in advance!

Answer 1

The error message indicates that the server host name is in error. This is because the instance name has been tacked onto the end of server host name (wwe**pe.**.***.ds\MSSQL***). While this is valid syntax using OLEDB or .NET drivers, the jTDS driver requires the host name (www**pe.**.***.ds) to be specified separately from the instance name (MSSQL***). The instance name must be supplied as a connection property.

At step 5 of the connection wizard, specify the Hostname alone:

At step 6, specify the instance name as a property:

At this point, it should be possible to establish a connection (assuming the other connection parameters are correct).