Narveson has asked for the wisdom of the Perl Monks concerning the following question:

My team uses Perl (v 5.8.9, ActiveState) with DBD::ODBC to query various SQL Server databases. We mostly use the "dbi:ODBC:$DSN" form of connection string, since we have the ability to create system DSNs as needed.

This setup has been working fine under Windows Server 2003, but now we are trying to migrate to Windows Server 2008, and my database connections are failing.

Here's the error: DBI connect('OurDatabase','',...) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002)

Should I be focusing on specifying a default driver (if so, what and how?), or should I be focusing on the way I set up the OurDatabase data source name?

Update

Thanks to both of you for suggesting DSNless connections. I have gone and verified that I can connect without a DSN by saying

    my $connection 
        = 'dbi:ODBC:driver={SQL Server};'
        . 'Server=OurServer;'
        . 'Database=OurDatabase;'
        . 'Trusted_Connection=Yes'
        ;
    my $dbh = DBI->connect(
        $connection,
        undef,
        undef,
        { RaiseError => 1 }
    );

[download]

so this may be my solution. It means changing existing code (did we think we could migrate without any changes?), but it also means I can defer having to figure out the new security model that seems to be hiding my new system DSNs from my legacy code.

Replies are listed 'Best First'.
Re: DBD::ODBC in Windows Server 2008
by Corion (Patriarch) on May 05, 2010 at 07:11 UTC

    I would assume that whatever user your script is running under does not see the DSN OurDatabase. For an administrative approach, I like setting up DSNs using odbcad32.exe, that way, the DSN together with the credentials is available from about any ODBC-enabled application.

    For the same reason, it might be inadvisable to store the DSN. Then, I would go for a DSNless ODBC approach, using information from http://www.connectionstrings.com/ on how to connect to whatever database there is.

[reply]
[d/l]
[select]
Re: DBD::ODBC in Windows Server 2008
by mje (Curate) on May 05, 2010 at 16:23 UTC

    I would presume your DSNs do not exist for the user you are logged in as. Creating them as SYSTEM DSNs is one way to fix this. Alternatively, you have migrated from Windows 32bit to 64bit and these use two different ODBC administrators - there is a 32bit one and a 64bit one - you need to pick the right one for your application.

    The error text including the "default driver specified" is a little misleading. In ODBC you can create a DEFAULT data source which is used if you do not specify a DSN - strangely ;-) it is implemented as data source named DEFAULT in the odbc ini files or registry entries.

    You can move to DSNless connections but that does mean if you connect from multiple apps and you want to change some aspect of the driver connection you will have to change it in multiple places instead of in a single SYSTEM DSN.

[reply]

      Good advice, and a perceptive warning:

      that does mean if you connect from multiple apps and you want to change some aspect of the driver connection you will have to change it in multiple places instead of in a single SYSTEM DSN.

      I think I'll set up a module so my client scripts can say

      use OurDatabase::Connection;
my $dbh = OurDatabase::Connection->new();

      [download]

      and the OurDatabase::Connection module can check whether it's running in development or in production and pick the right server name accordingly.

      My boss may regret the apparent increase in complexity, but I'll try to point out that we'll be leaving behind our dependency on crucial data that we've been keeping hidden in the Windows registry until now.

[reply]
[d/l]

Back to Seekers of Perl Wisdom