pyodbc 通过 IIS7 连接到 MSSQL 2005 服务器

2022-01-25 00:00:00 python pyodbc sql-server-2005 iis-7

问题描述

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

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='SERVERINSTANCE', 
                  Trusted_Connection='yes', 
                  database='Test_Chad', 
                  uid='SERVERusername', 
                  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 AUTHORITYANONYMOUS 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='SERVERINSTANCE',  
                      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::p>

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='SERVERINSTANCE',  
                      database='Test_Chad', uid='username', pwd='password')

相关文章