Connect to remote MSSQL database with DBD-ODBC?

[FinnR]

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";

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";

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

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! :-)