in reply to Connect to remote MSSQL database with DBD-ODBC?

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;

[download]

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.

Replies are listed 'Best First'.
Re^2: Connect to remote MSSQL database with DBD-ODBC?
by FinnR (Novice) on Dec 14, 2005 at 14:41 UTC
    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?
[reply]

      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.

[reply]
        I totally agree with everything you write. The customer actually previously has used a consultant with special SQL DBA skills, so I'll make sure he will take care of this instead of me. ;-) This is not my job - you're right.

        Would be nice to learn more about it for my own sake, though, since it's quite basic to make my part of the job to go online, so I'll take a closer look at the SQL Server Books Online.

        As for the security issue; you are right again, and the plan is definitely to use another port than the default (1433), plus restricting the database access to connections from the webserver's IP address. The code I posted is simply a "first stage connection test code", so far I've only tried it with a test installation of MSSQL on one of our own servers. :-)

        Who knows, maybe I'll write a little tutorial when I'm done - something like a "Step-by-step tutorial for ALL you need to know about connecting to a remote MSSQL database server from your local Perl script using DBI-ODBC". (Including all neccessary tasks on both servers). It believe it would be appreciated by many a monk. Take care! :-)
[reply]

In Section Seekers of Perl Wisdom