Hello,

I have a Perl script on my company's Win2000 Server with IIS, and need to connect to a MSSQL database running on a customer server (Win2000 Server). These two servers are on totally different networks, not related or connected to each other whatsoever. Both servers are connected to the Internet, each with their own static IP-address.

I have no problem using DBD-mysql to connect to remote MySQL-databases. Neither do I have any problem connecting to databases running on the SAME server as the Perl-script using DBD-ODBC via DSNs. However, I can't figure out how to connect to a remote MSSQL database via DBD-ODBC. Seems like many people have asked questions about this on several Perl-related forums, but I just can't find any very clear replies. I really have read A LOT.

What I have figured out, is that I can create a Data Source Name on our webserver referring to the remote MSSQL-database (with IP-address, username, password). The Perl-script then can use this DSN to connect to the remote MSSQL-server, i.e. like this: 
use DBI; 
$DSN = "my_datasourcename"; # Name of the DSN referring to the remote 
+MSSQL-database with IP-adress, logins etc. 
$dbh = DBI->connect("dbi:ODBC:$DSN;or die "$DBI::errstr\n";

[download]
However, I can't get this to work. Is there a tutorial etc. on how to set up a DSN (ODBC) in Windows to a *remote* SQL Server. How do I tell MSSQL that it should accept connections from a remote server - in this case with TCP via the Perl-script). Do I need to add a Server Alias (IP-address) in MSSQL's "Client Network Utility"? I would assume that creating a ODBC-DSN on the server with the Perl-script containing information about the MSSQL-server's IP-address, username, password etc. is a good start...? All this is so very simple with MySQL-databases using DBD-mysql..., but I'm kind of stuck here... so anybody?

I have also tried to do something like this - i.e. connecting directly to the database with some kind of "remote DSN on the fly": 
use DBI;
# Test code for connecting directly to a remote MSSQL database WITHOUT
+ a predefined data source name... 
$database = "TempoPlan"; 
$hostIP = "111.222.333.444"; # Example IP-adress for this post
$port = "1433"; 
$servername = "MSSQL_SERVERNAME"; 
$user = "username"; 
$password = "password"; 

$DSN = qq|driver={SQL Server (32 bit)};Server=$servername; database=$d
+atabase;port=$port;uid=$username;pwd=$password;|; 
$dbh = DBI->connect("dbi:ODBC:$DSN;host=$hostIP;") or die "$DBI::errst
+r\n";

[download]
For all variations I try of the code above, I get an error message like this: 
DBI connect('driver={SQL Server (32 bit)};Server=MSSQL_SERVERNAME; dat
+abase=TempoPlan;uid=username;pwd=password;;host=111.222.333.444;','',
+...) failed: [Microsoft][ODBC Driver Manager] Data source name not fo
+und and no default driver specified

[download]
I'm sorry if this is just as much a MSSQL-related question as it is Perl-related. Still, I guess many people would appreciate a solution on how to connect to a remote MSSQL database via DBD-ODBC. If there already is a step-by-step tutorial etc. somewhere out there on how to do this, *please* let me know! :-)

BTW: My Perl-version is ActivePerl 5.8.7, build 813.
Any help will be highly appreciated! :-)

In reply to Connect to remote MSSQL database with DBD-ODBC? by FinnR

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.