Perl DBI : Connect to SQL Server that uses Windows Authentication

Question

我正在使用 perl DBI 连接到 SQL Server。此服务器使用 Windows 身份验证。如何通过 DBI 将此信息传递到 SQL 服务器？

Answer 1

我假设“使用 Windows 身份验证”意味着您的脚本在 Windows 上运行。那么有不止一种方法可以做到这一点。您可以使用 ADO 而不是 ODBC 并且不需要创建 DSN:

  my $db_instance = ".\\SQLEXPRESS";
  my $db_name     = "AdventureWorks";
  my $db_user     = "sa";
  my $db_pass     = "...";

  my $n = 0;
  for my $cs (
      "DBI:ODBC:Driver={SQL Server};Server=$db_instance;Database=$db_name;UID=$db_user;PWD=$db_pass"
    , "DBI:ODBC:Driver={SQL Server};Server=$db_instance;Database=$db_name;"
    , "DBI:ODBC:DSN=AdvWork;"
    , "DBI:ADO:DSN=AdvWork;"
    , "DBI:ADO:Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=$db_name;Data Source=$db_instance;"
    , "DBI:ADO:Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;PWD=$db_pass;Initial Catalog=$db_name;Data Source=$db_instance;"
    , "DBI:ADO:Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID=\"\";Initial Catalog=$db_name;Data Source=$db_instance;Initial File Name=\"\";Server SPN=\"\";"
  ) {
     my $dbh = DBI->connect($cs) or die("\nCONNECT ERROR:\n$DBI::errstr");
     my $wtf = $cs;
     $wtf =~ s/$db_pass/secret/;
     printf "%2d CS: '%s'\n", ++$n, $wtf;
     $dbh->disconnect();
  }

输出:

xpl.pl::Main started using Perl 5.010000 on MSWin32.
will call CnctMSSS - connect to MS SQLServer (SQLExpress)
 1 CS: 'DBI:ODBC:Driver={SQL Server};Server=.\SQLEXPRESS;Database=AdventureWorks;UID=sa;PWD=secret'
 2 CS: 'DBI:ODBC:Driver={SQL Server};Server=.\SQLEXPRESS;Database=AdventureWorks;'
 3 CS: 'DBI:ODBC:DSN=AdvWork;'
 4 CS: 'DBI:ADO:DSN=AdvWork;'
 5 CS: 'DBI:ADO:Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Advent
ureWorks;Data Source=.\SQLEXPRESS;'
 6 CS: 'DBI:ADO:Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;PWD=secret;Initial Catalog=Adventur
eWorks;Data Source=.\SQLEXPRESS;'
 7 CS: 'DBI:ADO:Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial C
atalog=AdventureWorks;Data Source=.\SQLEXPRESS;Initial File Name="";Server SPN="";'
CnctMSSS returned 0 [5.858424 secs]
xpl.pl::Main done. (0)

如果比较 (1) [基于 this contribution ] 和(2)，你看到跳过用户信息就足以连接到数据库；某些驱动程序/提供商可能喜欢添加“集成安全性 = SSPI”。

Here如果您在试验连接字符串方面需要帮助，这是一个很好的起点。