SQL Server 2012经典asp连接字符串

I have a SQL Server 2012 Express installation with a new DB called BRD that I have created. I have also created a test table (tempDemo), and a test stored procedure (getStList) in the BRD database. The stored procedure works when I run it in the query window so I believe the table and stored procedure are legit. The SQL Server is set to "SQL Server and Windows Authentication mode".

我有一个SQL Server 2012 Express安装，并创建了一个名为BRD的新DB。我还在BRD数据库中创建了一个测试表(tempDemo)和一个测试存储过程(getStList)。当我在查询窗口中运行存储过程时，我认为表和存储过程是合法的。将SQL服务器设置为“SQL Server和Windows身份验证模式”。

I then attempted to create a Classic ASP page that then connected to the SQL Server using the following connection string:

然后我尝试创建一个经典的ASP页面，然后使用以下连接字符串连接到SQL服务器:

objConn.ConnectionString="Provider=SQLOLEDB;Server=XPSI7\SQLEXPRESS;Database=BRD;Integrated Security=SSPI;"

This fails with the following message:

这在以下消息中失败:

"Microsoft OLE DB Provider for SQL Server error '80004005' Cannot open database "BRD" requested by the login. The login failed."

“Microsoft OLE DB Provider for SQL Server error '80004005'无法打开登录请求的数据库BRD”。登录失败了。”

When I change the database to MASTER instead of BRD the ASP page does not error out. I'm just testing the connection string by opening it and then closing it, but it appears to work.

当我将数据库改为MASTER而不是BRD时，ASP页面不会出错。我只是通过打开连接字符串然后关闭它来测试连接字符串，但是它似乎可以工作。

I've looked at the security settings for MASTER and BRD in the Object Explorer, but have failed to notice a difference. I've also looked at the IIS_IUSRS for the folders, but no difference either - not sure if this is necessary anyway.

我在Object Explorer中查看了MASTER和BRD的安全设置，但是没有注意到其中的差异。我还查看了文件夹的IIS_IUSRS，但也没有区别——我不确定这是否必要。

2 个解决方案

#1

SQL Server authenticates your login at the server level. Then it tries to open the database you've asked to connect to. At this point, you need to either map a database-level user to the server-level login, or use a server-level login that inherently has sufficient privileges to use the database.

SQL Server在服务器级别验证您的登录。然后它尝试打开您要连接的数据库。此时，您需要将数据库级别的用户映射到服务器级别的登录，或者使用具有足够权限的服务器级别登录来使用数据库。

A simple (but not secure) way to demonstrate this using SQL Server Authentication:

使用SQL Server身份验证来演示这一点的一种简单(但不安全)方法:

USE master;
GO
CREATE LOGIN foo
  WITH PASSWORD = 'bar', CHECK_POLICY = OFF;
GO
USE BRD;
GO
CREATE USER foo FROM LOGIN foo;
GO
EXEC sp_addrolemember N'db_owner', N'foo';
GO

Now in your ASP connection string, use:

现在在您的ASP连接字符串中，使用:

objConn.ConnectionString = "Provider=SQLNCLI;Data Source=XPSI7\SQLEXPRESS;Initial Catalog=BRD;User ID=foo;Password=bar;"

objConn。ConnectionString = "Provider=SQLNCLI;Data Source=XPSI7\SQLEXPRESS;初始目录=BRD;User ID=foo;Password=bar;"

You can also map whatever login you're using to a database user simply doing:

您还可以将您正在使用的任何登录名映射到数据库用户，只需执行以下操作:

USE BRD;
GO
CREATE USER [YourDomain\IIS_IUSRwhatever] 
  FROM LOGIN [YourDomain\IIS_IUSRwhatever];
GO

That will grant them access to the database, but it will be up to you to decide what permissions to grant. This assumes that you are allowing anonymous access to the web server; if IIS is challenging and accepting Windows authentication, you'll need to do the above for the user(s) that will be submitting their credentials. In either case you should be able to continue using the connection string you have now.

这将授予它们对数据库的访问权，但授予哪些权限将由您决定。这假定您允许匿名访问web服务器;如果IIS具有挑战性并接受Windows身份验证，则需要为提交其凭据的用户执行上述操作。无论哪种情况，您都应该能够继续使用您现在拥有的连接字符串。

#2

Try the following connection string:

尝试以下连接字符串:

strConnection = "Provider=SQLNCLI10;Server=server_name;Database=" & BD_REMOTE_INITIAL_CATALOG & ";Uid=" & BD_REMOTE_USER_ID & "; Pwd=*****;"

#1