Connect to remote Ms-sql Database

Question

I can connect to the local Database(installed in my system) from Mathematica via

SQLConnection[JDBC["Microsoft SQL Server(jTDS)", "localhost:1433/TestDatbase"], 
"Catalog" -> Automatic, "Description" -> None, "Name" -> "DemoSql", 
"Password" -> "test", "Properties" -> {}, "ReadOnly" -> False, 
"RelativePath" -> False, "TransactionIsolationLevel" -> "ReadUncommitted", 
"UseConnectionPool" -> False, "Username" -> "DemoLogin", "Version" -> 2.]

But When I try to connect to remote database I can't. I have following details

Sever Name: xxxx7ezff6.database.windows.net
User Name: xxxxxxxx
password: password
Database: AdventureWorks2012

I can see this remote database in my SQL Server Management Studio(SSMS) by using server name, user name and password. But I don't know how can I connect to this remote database from Mathematica. I tried like this

SQLConnection[JDBC["Microsoft SQL Server(jTDS)", 
"xxxx7ezff6.database.windows.net:1433/AdventureWorks2012"], 
"Catalog" -> Automatic, "Description" -> None, "Name" -> "AdvTest", 
"Password" -> "password", "Properties" -> {}, "ReadOnly" -> False, 
"RelativePath" -> False, "TransactionIsolationLevel" -> "ReadUncommitted", 
"UseConnectionPool" -> False, "Username" -> "xxxxxxxx", "Version" -> 2.]

It's end up with error. I think there is problem with hostname and port, But I don't have these details. How can I connect to this remote database?

Note: I don't have IP address as well. The port Number I have given here has been set by mathematica as default. I tried without port number as well, but no use.

Answer 1

You can try changing the driver and, instead of the MSFT driver "Microsoft SQL Server(jTDS)", use the "jtds_sqlserver" driver. I'm using it to connect to my remote SQL Server and works fine.

Answer 2

I have fought with this problem for a while now and have finally solved it. I tried connecting using windows authentication first as per this post but I just couldn't get it to work. I also tried to use the GUI (wizard) to create the connection but this seemed buggy. The details below refer to a database on a server which has the Mathematica network licence manager on it as well as Mathematica 8 installed on it. I use Mathematica 8 on my local machine with one of the licences from the network licence.

This is how i got it to work:

1. I made sure that I could connect via SQL Server Management Studio to the database on the server from my local machine using the SQL Authentication credentials I had. I could connect thus I knew there were no network problems and my SQL Authentication credentials were working.

2. I found the correct name of the server - it was the name as used when connecting via SQL Server Management studio but without the SQL instance included.

3. I selected two databases that I wanted to connect to - they are called master and model

4. I compiled a notebook as below and was able to connect.

   Loading the package

   Needs["DatabaseLink`"]

Details: ECSDBN-SRV-09.enerserv.local sa Password Catalog: master 192.168.1.11 Open the connection

conn = OpenSQLConnection[
  JDBC["Microsoft SQL Server(jTDS)", "ECSDBN-SRV-09/model"], 
  "Username" -> "sa", "Password" -> "N3tC0nf1g"]

SQLConnection[1, "Open", "Catalog" -> "model", 
 "TransactionIsolationLevel" -> "ReadCommitted"]

conn1 = OpenSQLConnection[
  JDBC["Microsoft SQL Server(jTDS)", "ECSDBN-SRV-09/master"], 
  "Username" -> "sa", "Password" -> "N3tC0nf1g"]

SQLConnection[2, "Open", "Catalog" -> "master", 
 "TransactionIsolationLevel" -> "ReadCommitted"]

(*Here I have not specified the database nane after the server name and it just seems to pick the one on the top of the database list.*)

conn2 = OpenSQLConnection[JDBC["Microsoft SQL Server(jTDS)", "ECSDBN-SRV-09"],
   "Username" -> "sa", "Password" -> "N3tC0nf1g"]

SQLConnection[3, "Open", "Catalog" -> "master", 
 "TransactionIsolationLevel" -> "ReadCommitted"]

(*Look at the open connection to make sure.*)

SQLConnections[]

{SQLConnection[1, "Open", "Catalog" -> "model", 
  "TransactionIsolationLevel" -> "ReadCommitted"], 
 SQLConnection[2, "Open", "Catalog" -> "master", 
  "TransactionIsolationLevel" -> "ReadCommitted"], 
 SQLConnection[3, "Open", "Catalog" -> "master", 
  "TransactionIsolationLevel" -> "ReadCommitted"]}

(*Check what tables exist through the current connection*)

SQLTables[conn]

{}

SQLTables[conn1]

{SQLTable["MSreplication_options", "TableType" -> "TABLE"], 
 SQLTable["spt_fallback_db", "TableType" -> "TABLE"], 
 SQLTable["spt_fallback_dev", "TableType" -> "TABLE"], 
 SQLTable["spt_fallback_usg", "TableType" -> "TABLE"], 
 SQLTable["spt_monitor", "TableType" -> "TABLE"], 
 SQLTable["spt_values", "TableType" -> "TABLE"]}

SQLTables[conn2]

{SQLTable["MSreplication_options", "TableType" -> "TABLE"], 
 SQLTable["spt_fallback_db", "TableType" -> "TABLE"], 
 SQLTable["spt_fallback_dev", "TableType" -> "TABLE"], 
 SQLTable["spt_fallback_usg", "TableType" -> "TABLE"], 
 SQLTable["spt_monitor", "TableType" -> "TABLE"], 
 SQLTable["spt_values", "TableType" -> "TABLE"]}

(*Check that I can access data from one of the tables in the connection using the SQL query approach*)

SQLExecute[conn1, "SELECT * FROM MSreplication_options", 
  "GetAsStrings" -> True] // InputForm

{{"transactional", "1", "90", "0", "0", "0"}, {"merge", "1", "90", "0", "0", "0"}, {"security_model", "1", "90", "0", "0", "0"}}

(*Try another server*)

conn = OpenSQLConnection[
  JDBC["Microsoft SQL Server(jTDS)", "ECSDBN-SRV-04.enerserv.local"], 
  "Username" -> "sa", "Password" -> "P@ssw0rd"]

JDBC::error: Network error IOException: Connection refused: connect >>

$Failed

However when I tried the same thing with another server which does not have Mathematica installed on it (this may or may not be the problem) I could not connect. Also, the server I was able to connect to has two seperate instances of SQL on it - SQL 2005 and SQL 2008. Mathematica connected me to the 2005 instance by default and I can't find out how to connect to the 2008 instnace. I hope this helps a little bit.

Reference material: DatabaseLink/tutorial/Overview

Answer 3

Use this code : 

public class ConnectionClass {
    String ip = "192.168.0.131";
    String classs = "net.sourceforge.jtds.jdbc.Driver";
    String db = "Andro";
    String un = "sa";
    String password = "Admnsql";
    @SuppressLint("NewApi")
    public Connection CONN() {
    StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder()
    .permitAll().build();
    StrictMode.setThreadPolicy(policy);
    Connection conn = null;
    String ConnURL = null;
    try {
    Class.forName(classs);
    ConnURL = "jdbc:jtds:sqlserver://" + ip + ";"
    + "databaseName=" + db + ";user=" + un + ";password="
    + password + ";";
    conn = DriverManager.getConnection(ConnURL);
    } catch (SQLException se) {
    Log.e("ERRO", se.getMessage());
    } catch (ClassNotFoundException e) {
    Log.e("ERRO", e.getMessage());
    } catch (Exception e) {
    Log.e("ERRO", e.getMessage());
    }
    return conn;
    }
}

Answer 4

Here is the connection string that I use to SQL Server connection:

conn=OpenSQLConnection[
   JDBC["Microsoft SQL Server(jTDS)", "185.50.123.132"], 
   "Username" -> "myUser", 
   "Password" -> "MyPass" 
]

Have you tried using the direct IP of your remote machine?