Re: Connect to remote MSSQL database with DBD-ODBC?

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.

Comment on Re: Connect to remote MSSQL database with DBD-ODBC?

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