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?

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

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.

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

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.

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

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?

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.

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?

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.

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.

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!