通过IIS7与MSSQL 2005服务器的pyodbc连接 [英] pyodbc connection to MSSQL 2005 server via IIS7

问题描述

我在笔记本电脑上安装并配置了IIS7，并带有python cgi界面.我可以从Eclipse中运行python脚本，并从数据库中获取所需的结果.但是，当我从网页运行脚本时，出现身份验证错误.似乎连接字符串没有将凭据传递给SQL Server.有什么想法吗?

I have IIS7 installed and configured on my laptop and with a python cgi interface. I can run the python script from within Eclipse and get the results I am looking for from the database. But when I run the script from the webpage I get an authentication error. It seems that the connection string is not passing the credentials to the SQL server. Any thoughts?

import pyodbc
import cgi
import cgitb; cgitb.enable()

cnxn = pyodbc.connect(driver='{SQL Server}', 
                  server='SERVER\INSTANCE', 
                  Trusted_Connection='yes', 
                  database='Test_Chad', 
                  uid='SERVER\username', 
                  pwd='password')

def getRow():
    cursor = cnxn.cursor()
    cursor.execute("select user_id, user_name from users")
    row = cursor.fetchone()
    print 'name:', row.user_id     
    print 'name:', row.user_name

getRow()

例外:

<class 'pyodbc.Error'>: ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\\ANONYMOUS LOGON'.
(18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\\ANONYMOUS LOGON'. (18456)") 
args = ('28000', r"[28000] [Microsoft][ODBC SQL Server Driver][SQL ... for user 'NT AUTHORITY\ANONYMOUS LOGON'. (18456)") 
message = ''

推荐答案

确定要使用的身份验证形式是数据库还是Windows.您不能同时使用两者.

Decide which form of authentication you want to use, database or Windows. You can't use both.

如果要使用Windows身份验证，请将连接设置更改为以下内容:

If you want to use Windows authentication, change your connection setup to the following:

cnxn = pyodbc.connect(driver='{SQL Server}', server='SERVER\INSTANCE',  
                      database='Test_Chad', trusted_connection='yes') 

通过Windows身份验证将Web应用程序连接到数据库需要一些其他配置.如果您的Web应用程序在IIS中使用匿名身份验证，则将使用运行Web应用程序的应用程序池的标识来建立数据库连接.这是设置此身份的屏幕截图:

Connecting a web application to a database via Windows authentication requires some other configuration. If your web application uses anonymous authentication in IIS, the database connection will be made using the identity of the application pool in which the web app runs. Here is a screenshot of where this identity is set:

如果要使用简单的数据库身份验证，请为 uid 和 pwd 提供适当的值，并删除 Trusted_Connection = yes :

If you want to use simple database authentication, provide the appropriate values for uid and pwd, and remove Trusted_Connection=yes:

cnxn = pyodbc.connect(driver='{SQL Server}', server='SERVER\INSTANCE',  
                      database='Test_Chad', uid='username', pwd='password')

这篇关于通过IIS7与MSSQL 2005服务器的pyodbc连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！