Re: Connecting to SQL Server on Windows

On 28/06/12 23:18, Shrenuj Bansal wrote:
> On Thursday, June 28, 2012 3:26:57 PM UTC-6, "Martin J.
> Evans" wrote:
>> On 28/06/2012 21:03, Shrenuj Bansal wrote:
>>> On Thursday, June 28, 2012 11:50:50 AM UTC-6, "Martin J.
>>> Evans" wrote:
>>>> On 28/06/2012 17:07, Shrenuj Bansal wrote:
>>>>> I am using a perl script to try to connect to SQL Server on
>>>>> Windows. I believe I have DBI v1.607 and DBD::ODBC v1.25. I
>>>>> am able to connect to the server when I set up a data source
>>>>> using the ODBC Data Source Administrator on Windows. However,
>>>>> when I try to set up the data source myself in the perl
>>>>> script with the same credentials I get an error. I was hoping
>>>>> someone could help me out with this.
>>>>>
>>>>> Here's my code:
>>>>>
>>>>>
>>>>> use DBI;
>>>>>
>>>>> # DBD::ODBC
>>>>>
>>>>> my $dsn = 'DBI:ODBC:Driver={SQL Server}'; my $host = my
>>>>> $database = my $user = my $auth =
>>>>>
>>>>> # Connect via DBD:ODBC by specifying the DSN dynamically my
>>>>> $dbh = DBI->connect("$dsn;Server=$host;Database=$database",
>>>>> $user, $auth, { RaiseError => 1, AutoCommit => 1} ) || die
>>>>> "Database connection not made: $DBI:errstr";  # Prepare a SQL
>>>>> statement
>>>>>
>>>>> my $sql = "SELECT BundleVersionLocation FROM
>>>>> valdb.dbo.VDB_BundleVersions WHERE BundleVersionID = 20"; my
>>>>> $sth = $dbh->prepare( $sql );
>>>>>
>>>>> # Execute the statement $sth->execute();
>>>>>
>>>>> my($BundleVersionID);
>>>>>
>>>>> # Bind the results to the local variables $sth->bind_columns(
>>>>> undef, \$BundleVersionID );
>>>>>
>>>>> # Retrieve values from the result set while( $sth->fetch() )
>>>>> { print "$BundleVersionID\n"; }
>>>>>
>>>>> # Close the connection $sth->finish(); $dbh->disconnect();
>>>>>
>>>>> Here is the error I receive when I try to run the above
>>>>> script:
>>>>>
>>>>> DBI connect('Driver={SQL
>>>>> Server};Server=ecmdrvdev-dbsql;Database=valdb','shrenuj
>>>>> b',...) failed: [Microsoft][ODBC SQL Server Driver][SQL
>>>>> Server]Login failed for user 'shrenujb'. (SQL-28000) at
>>>>> test.pl line 13
>>>>>
>>>> You did not specify a UID/PWD in the above Perl and yet the
>>>> error indicates you did specify a username! If you want to use
>>>> a trusted connection you'll need to add that attribute to the
>>>> connection string.
>>>>
>>>> You can use dbi:ODBC:DSN=mydsn to connect in Perl where mydsn
>>>> is the data source you created in the ODBC administrator and
>>>> then once you connect print out odbc_out_connect_string
>>>> (http://search.cpan.org/~mjevans/DBD-ODBC-1.37/ODBC.pm#odbc_out_connect_string)
>>>>
>>>>
and that will be the connection string you need to use to reconnect
>>>> without a DSN once you replace DSN with DRIVER=xxx (you get
>>>> this string from the ODBC administrator driver tab and enclose
>>>> it in {} if it contains spaces).
>>>>
>>>> e.g. (on windows so quotes different),
>>>>
>>>> C:\Users\martin>perl -le "use DBI;my $h =
>>>> DBI->connect('dbi:ODBC:DSN=asus2','xx' ,'yy'); print
>>>> $h->{odbc_out_connect_string};"
>>>> DSN=asus2;UID=xx;PWD=yy;WSID=ASUS2;DATABASE=master;Network=DBMSSOCN;Address=ASUS2\SQLEXPRESS,1433
>>>>
>>>>
>>>>
Replace DSN=asus2 with DRIVER={SQL Server} (or SQLNative Client or SQL
>>>> Server Native Client 10.0 depending on what the Drivers tab
>>>> says).
>>>>
>>>> NOTE: If you put the db username/password in the connection
>>>> string via UID=xx;PWD=yy the username/password passed to DBI's
>>>> connect method are ignored - I think (and I say I think because
>>>> you are using a pretty old DBD::ODBC).
>>>>
>>>> Martin
>>> Sorry I forgot to specify that the host, database, user, and auth
>>> fields are assigned values. I just removed those since I did not
>>> want to display those in a public forum.
>>>
>> So given the information you've provided, the username or password
>> are wrong, that is what the error message says. You'll have to give
>> me more information about what works compared with what does not
>> work if you want more help.
>>
>> Martin
>
> So I wrote the above script with the host, database, user and auth
> fields filled in correctly. When I ran the script, it gave me the
> error shown saying the login failed.

and here you were using SQL Server authentication i.e., you passed a username and password which is given to SQL Server to verify and it said your credentials are invalid.

> I next went to the ODBC Data
> Source Administrator - System DSN tab and created a new DSN. I named
> it "sql_server_dsn" and for the question "Which SQL Server do you
> want to connect to" I entered the same name as in the $host field.
> For the question "How should SQL Server verify the authenticity of
> the login ID?" I chose "With Windows NT authentication using the
> network login ID".

and here you used windows NT authentication so you don't need to pass in a username and password - in other words you used a trusted connection.

> I let the rest of the options in the configuration
> remain as they are. I replaced the
> DBI->connect("$dsn;Server=$host;Database=$database", $user, $auth, {
> RaiseError => 1, AutoCommit => 1}  line in the script with
>
> DBI->connect("dbi:ODBC:sql_server_dsn", $user, $auth, { RaiseError =>
> 1, AutoCommit => 1} and then ran the script. It worked and gave me
> the correct result to the query.

but your DSN said use a trusted connection and as you were already logged in the username and password passed to connect is irrelevant.

> In the user and auth fields I had
> put my windows username and password for both cases. I need to have
> this working so that I can just create the DSN in the perl script and
> not have to create it using the ODBC Data Source Administrator. I am
> not sure if there is something wrong with my format for the
> DBI->connect line or there are login problems. Any help would be
> greatly appreciated. Thanks
>

It sounds like you want a DSN-less connection. How you do this depends on whether a) you need to use a trusted connection or b) have a username/password for SQL Server but in both cases your connection string starts with dbi:ODBC:DRIVER={xxx} and does not contain DSN.

If (a) you need to add an attribute to the connection string to tell SQL Server to use a trusted connection. Add Trusted_Connection=True or Integrated Security=SSPI (kerberos or fallback NTLM) but it depends on which driver you are using.

If (b) you need to pass valid SQL Server username/password to DBI's connect.

There are loads of sites with example connection strings for SQL Server. Just make sure you use DRIVER= in uppercase as DBD::ODBC has a problem identifying the DRIVER in the connection string on unicode builds if it is mixed or lower case (basically the XS code does not case fold on unicode strings).

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com