4

I have two computers with two (ostensibly) identical installations of SQL Server Express 2008 R2, both running Windows 7 64-bit. On one computer (call it "red"), I can execute the following mathematica code to open a connection to the database and read data

Needs["DatabaseLink`"]

conn = OpenSQLConnection[
  JDBC[
   "Microsoft SQL Server(jTDS)",
   "localhost"],
  "Instance" -> "SQLExpress"]

I have the jTDS driver for sql server installed in c:\windows\system32\ntlmauth.dll. I copied the dll from the working machine "red" to the non-working machine "black."

I used the SQL-Server import and export tool to transfer a very simple database from "red" to "black" and verified that the database is accessible by running LinqPad on "black" and reading data. All good.

Now, I try to run the Mathematica code above on "black" and I get an undiagnosable error message, namely:

JDBC::error: "!(TraditionalForm`\"Network error IOException: Connection refused: connect\") "

I just know this is going to be one of those nightmarish permission issues with the localservice account or the network-service account. I do not have Sql-Server Management Studio on machine "black" and I was unable to find the appropriate version of SSMS to install for SQL Server 2008 R2 Express (the SSMS Express 2008 version does not install, citing "known compatibility issues.") I don't really miss SSMS since LinqPad works fine for my development tasks.

I do not know how to diagnose or workaround or proceed in any way -- I'm completely blocked and would be very grateful for advice or guidance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Reb.Cabin
  • 5,426
  • 3
  • 35
  • 64
  • 1
    I am trying to reproduce your issue: Do you use the 32-bit version of 'SQL Server 2008 R2 Express' or the 64 bit version (they give you a choice here: http://www.microsoft.com/betaexperience/pd/SQLEXP08V2/enus/) – Arnoud Buzing Jan 10 '12 at 15:02
  • 64-bit version of sql server 2008 r2 express, sp1 im am pretty sure. It's also the 64-bit version of ntlmauth.dll. – Reb.Cabin Jan 10 '12 at 16:20

2 Answers2

3

Connection Error while connecting to SQL Server. It is not an authentication error.

It is just that may be your SQL Server is not configured to accept request through

through the transport protocol that you are using. Open SQL Server Surface Configuration

Manager and allow to accept connection from all ways provided.

Acn
  • 1,010
  • 2
  • 11
  • 22
  • 1
    I found "Sql Server Configuration Manager," and in there I found four client protocols: Shared Memory, Named Pipes, TCP/IP, and VIA. I made sure they're all enabled (they weren't!) in both the client section and the Sql Server Network Configuration section, restarted the SQL-Server service. Went to the firewall and added inbound and outbound rules for port 1433 allowing connections, and still no dice :( – Reb.Cabin Jan 10 '12 at 13:34
3

Somewhat guessing here, but I'd try

conn = OpenSQLConnection[ JDBC[ "Microsoft SQL Server(jTDS)", "localhost:1433;instance=SQLExpress"]]

I'm pretty sure OpenSQLConnection[] doesn't take an "Instance" option. It might work if passed like this:

conn = OpenSQLConnection[ JDBC[ "Microsoft SQL Server(jTDS)", "localhost:1433"], "Properties"->{"instance"->"SQLExpress"}]

Joshua Martell
  • 7,074
  • 2
  • 30
  • 37
  • incredibly, the following statement works when I connect to the "red" machine remotely, running the following on the "black" machine: `conn = OpenSQLConnection[JDBC[ "Microsoft SQL Server(jTDS)", "redMachine"], "Instance" -> "SQLExpress"]` but to access the local copy of the data on the black machine, i must use your first proposal. – Reb.Cabin Jan 12 '12 at 03:43
  • 1
    The "Instance" option is probably just ignored by OpenSQLConnection[]. – Joshua Martell Jan 13 '12 at 17:10