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

[JamesNC]

When you created the DSN using the ODBC Data Source Administrator (make sure you created a System DSN ) which is in the Control Panel under Administrative Tools. Did you get a successful test? If you did, then you could be having issues with the fact that your script is not running with the same credentials as you. Because NTLM is used by default you can do a $dbh=DBI->connect( 'dbi:ODBC:DSN_Name' ) or die $!. Your dba has to set up a seperate account for remote logins. They may give you a hassel about it, but I remember running into a similar problem once and NTLM was the issue. If however, you tested the DSN and it fails, then it is not MSSQL Server, but your network ( make sure the ports are open, a lot of firewall teams block everything now days ). Also, make sure your dba gives you the right group permissions for all the databases you want to access. You can google for tutorials. They abound for using DBI and setting up DSN and DSN-less connections.
JamesNC

[FinnR]

JamesNC wrote: "Your dba has to set up a seperate account for remote logins."

Seems like I need to set up this account / settings without the help of the customer... since their database admin doesn't seem to have any experience with how to do make that happen. I seems like it will be part of my job to make it work... The database server has been on the intranet for years (not connected to the Internet), but they now want to publish some of the data on the net - via a publishing tool (Perl) hosted on our server.

I know this is not a direct Perl-question (even though my Perl-code won't do any real work until I get that database connection up and running), but have you got any ideas on exactly how to set up an account for remote logins, that can be used for a connection via a ODBC DSN called by Perl on a remote machine?

[JamesNC]

Why don't you google for how to add user accounts? How to add user accounts to MSSQL This link seems relevant. Since this is pretty much OT at this point. You need to dig into the docs. ciao, JamesNC

[marto]

Hi FinnR,

Read the node Help with installation and use of the DBD::ODBC module, especially Errto's response.
It may be of help to you.

Hope this helps.

Martin

[terce]

I think that node addresses a slightly different problem - in Help with installation and use of the DBD::ODBC module, the poster wanted to connect to an ODBC DSN on a remote machine (from a Unix box).

In this case, if I'm reading it right, the OP is trying to connect to a DSN on the local machine, where the database to which the DSN connects is on a remote machine.

[pKai]

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...?

That would be the way to go, since you are already on Windows.

You set up the local DSN, either by the script itself or manually (Control Panel->Administrative Tools->Data Sources (ODBC)) with database server -name or -address (if you use a name the only requirement is, that it can be resolved on that machine) and name of the account you want to connect with (no entering of password required here, if not for actual testing the connection)).

When a DSN is present you then can connect to that (local) DSN declaring it your ODBC data source, giving DSN name, user name and password.

This ODBC connection is your proxy to the remote MSSQL server.

[terce]

You don't have to have an ODBC DSN set up to connect via ODBC. The second style of connecting which the OP lists in code is the so-called "DSN-less" method: ODBC drivers and connection strings are still used, but you don't have to create a DSN first.

Handy if you have to create a app for use on several hundred client machines, and don't want to configure a DSN on each one ;)

[FinnR]

Thanks a lot! pKai, that sounds very close to what I thought would be a good solution! However, when testing the connection I make manually via Administrative Tools->Data Sources(ODBC), it fails basically whatever information I add to the different text boxes / screens of the DSN dialog box, typically saying just "Test failed" etc.

I believe this must be a result of not having prepared MSSQL to accept such a remote connection. What do I need to do in MSSQL... do I need to set up "remote access" for that database - and how? What MSSQL tool do I use to do that? Do you (or anyone else) know of a web page where they go step-by-step through the different DSN configuration dialog box screens for MS SQL Server?

[terce]

UPDATE: Ignore me. Getting confused between MySQL and MSSQL

UPDATE2: I am currently using a connection string in the following format to successfully connect to an instance of MSSQL on another machine - (using DBIx::Simple, but the connection mechanics are the same):

my $conn = 'driver={SQL Server};Server=SERVERNAME;Database=DBNAME;uid=
+readonly;pwd=readonly';
my $db = DBIx::Simple->connect("dbi:ODBC:$conn") or die DBIx::Simple->
+error;

This is a DSN-less connection. The only major difference between my version and yours is the omission of " (32 Bit)" from the ODBC driver name. I vaguely remember that this harks back to the SQL 6.5 era, when there were 16- and 32-bit versions of the driver available.

To generalise, the connection string for DBD::ODBC should be the same as the connection string for any other Windows application. You can find helpful lists of connection strings for many database platforms (connection string types including ODBC, ADO, OLEDB etc.) at sites like this one.

[FinnR]

Thanks terce!
Is "SERVERNAME" the IP-address to the server where MSSQL is located?

I believe that both the DSN-less connection you suggest, and the one with the local DSN suggested by pKai, will work. However, I am getting more and more convinced that the main problem turns out to be on the MSSQL side of things -

1) How to actually set up a MSSQL-account etc. that can be used to connect to the MSSQL from a remote computer.
2) If using a local DSN to connect via, I am not quite sure about how to set that up with the neccessary information to connect to the remote MSSQL-machine / which information to enter where. (Where to enter the IP-address to the remote MSSQL-machine, etc.)

Since this is quite neccessary to let Perl into the game and get some data from MSSQL, do you have any idea (maybe a link?) on how I can do this?

[terce]

You need to follow the advice given by JamesNC here. Check the documentation - SQL Server Books Online is a good place to start. Test your connection using the the ODBC Administrator or the MSSQL query analyser - in either case make sure you select "SQL Server Authentication" (rather than "Windows Authentication") and enter the username/password you have been given by the remote site.

If the remote site's SQL DBA can't or won't help you with a username/password, you're pretty much stuffed - if neither of you know what you're doing, it'll only lead to more problems. The SQL Server Enterprise Manager makes tasks like adding a user pretty easy, but it's something the admin at the remote site will have to do. In any case, you don't need the help of Perl Monks. Try a Google search for "Enterprise Manager Tutorial".

In fact, I don't think either of these issues are really your problem. If the customer can't configure their system for you to access them, it's not your responsibility - they need to pay somebody with SQL DBA experience to help them out. If you help them on this, they'll be back to you for free DBA advice every five minutes till the end of time. This is a lesson I learned the hard way, and perhaps saying it here will ease the journey of a fellow monk.

As a side-note, I'm quite alarmed by the description of what you're trying to do you have given. It's an extremely bad idea for your customer to expose their SQL Server to the public internet on its default port (1433) - just search Google for "Code Red worm". Your customer needs to look into securing the server somehow - it may be as simple as adding a rule on the firewall to prevent connections to the SQL server from anywhere but your IP address.

[idsfa]

One likely major obstacle is that if their network admins have any clue whatsoever, your traffic is being blocked at their perimeter by a firewall. If not, their database is likely compromised anyway.

That said, why on Earth would you want to even consider connecting with plaintext to a customer database over the internet? Has anyone in your legal department (or theirs) reviewed the content for potential regulatory problems?

Get your network team talking to theirs about some method of an encrypted communications tunnel between these two servers. If you don't need the data in real time, you could also exchange it as an encrypted file.

[FinnR]

Thanks - I'll forward your input to someone else to see if they've thought of it. The programming is still on the testing stage. The code I posted has only been used with a test MSSQL installation on a remote demo server so far.

The idea was to get the basics to work here first, and then take the next step by connecting to the customer's server (with a none default port - not 1433, and with restrictions on which IPs are able to connect. The network part should be in good hands with the customer's network admin. Thanks again!