Server 2012 R2 网站连接问题上的 SQL Server 2008 R2 [英] SQL Server 2008 R2 on Server 2012 R2 web site connection issue

问题描述

我一直在使用这个连接字符串，在许多数据库和 MS 操作系统上都没有问题.Windows Server 2003、2008 R2 和 2012 R2 Eval，所有 Express 版本.

I have been using this connection string with no problem on a number of databases and MS operating systems. Windows Server 2003, 2008 R2 and 2012 R2 Eval, all Express versions.

我现在正在尝试部署到新的 Windows Server 2012 R2 安装，但出现了一些错误.我在 Windows Server 2012 R2 机器上安装了 SQL Server 2008 R2.

I am now trying to deploy to a new Windows Server 2012 R2 installation and I am getting some errors. I have the SQL Server 2008 R2 installation on the Windows Server 2012 R2 machine.

我的con字符串:

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDB.mdf;Integrated Security=True;User Instance=False" ProviderName="System.Data.SqlClient"

我认为这一定与新服务器上组件的安装顺序有关.

I think this must have something to do with the installation sequence of the components on the new server.

我过去能够简单地将 myDB.mdf 复制到基本上任何 Web 服务器并打开或访问数据库表.此 DB 包括与其关联的网站的 ASP 成员资格.

I have in the past been able to simply copy myDB.mdf to basically any web server and open or access the DB tables. This DB includes an ASP membership for the web site that it is tied to.

这里是错误:

在数据库master"中拒绝创建数据库权限.试图为文件附加一个自动命名的数据库C:\inetpub\techTran\App_Data\myDB.mdf 失败.一个数据库存在相同名称，或无法打开指定文件，或位于 UNC 共享上.
说明:在执行当前 Web 请求期间发生未处理的异常.请查看堆栈跟踪以获取有关错误及其在代码中的来源的更多信息.

CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file C:\inetpub\techTran\App_Data\myDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

异常详细信息:System.Data.SqlClient.SqlException:CREATE数据库 'master' 中的 DATABASE 权限被拒绝.

Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database 'master'.

我以为我明白这个错误是什么.我已检查权限，它们与部署此数据库的其他服务器相匹配.我的 SQL Server 安装基本上都是一样的.唯一的区别是这是 VM 上的 Windows Server 2012 R2.或者我没有像在过去设置的 5 个服务器中那样安装 SQL Server 2008 R2.(我猜可能是这种情况.)

I thought I understood what this error was. I have checked permissions and they match the other servers where this database has been deployed. My SQL Server installations are basically all the same. The only difference here is that this is Windows Server 2012 R2 on a VM. Or I did not install the SQL Server 2008 R2 the same way I have done in the past 5 servers I have set up. (Which I am guessing is probably the case.)

更新:卸载并重新安装 SQL Server 和 SSMS.

UPDATE: Uninstalled and reinstalled SQL Server and SSMS.

我知道服务器上只有一个名为 myDB.mdf 的数据库名称，所以我不相信错误的同名"部分.我为数据库添加了安全性，以便可以打开它，我什至在服务器上安装了 Visual Studio 2012 以确保可以打开数据库.我可以在本地主机(服务器 2012)上运行完整的应用程序，但我无法通过网络连接访问它.

I know there is only one database name on the server called myDB.mdf, so I don't believe the "same name" portion of error. I have added security to the db so that it can be opened and I even installed Visual Studio 2012 on the server to be certain that the db could be opened. I can run the full app on the localhost (server 2012) but I can not access it over a web connection.

我相信与

CREATE DATABASE 权限在数据库 'master' 中被拒绝

CREATE DATABASE permission denied in database 'master'

是问题所在.

我以前从未见过此错误.

I have never seen this error before.

我通常将 NETWORK SERVICE 帐户分配给数据库以允许访问，这也是设置的方式.我还认为这可能是连接字符串问题.但是这个相同的字符串现在已经在 4 个不同的服务器上运行了，没有任何变化.Server 2003、2008 R2 和 2012(演示平台)(所有 Express 版本)，现在在生产中失败了.

I typically assign NETWORK SERVICE account to the database to allow access and this is how this one is set up as well. I also thought this might be a connection string issue. But this same string has worked on 4 different servers now with no changes. Server 2003, 2008 R2, and 2012 (demo platform)(all Express versions), now in production this is failing.

首页加载，然后登录抛出上述错误.

The home page loads and then the login throws the above error.

推荐答案

SOLVED:我不确定这是怎么发生的，或者为什么没有发生.但是由于某种原因，SQL 的安装没有将 NETWORK SERVICE 帐户添加到 sysadmin 服务器角色.我将其与 SQL 2008 R2 也在运行的 SERVER 2003 安装交叉引用，并发现 sysadmin SQL Server 角色在我正常运行的环境中包含了 NETWORK SERVICE.因此，一旦我将用户帐户添加到此角色，一切都正常了.

SOLVED: I am not sure how this happened or perhaps why it didn't happen. But for some reason the installation of SQL did not add the NETWORK SERVICE account to the sysadmin Server Role. I cross referenced this with the SERVER 2003 installation where SQL 2008 R2 is also running, and discovered that the sysadmin SQL Server Role included NETWORK SERVICE in my correctly functioning environment. So once I added the user account to this role, everything came up fine.

我相信这可能是安装 SQL 2008 R2 期间的一个问题.我确实指定了 NETWORK SERVICE，但由于某种原因它没有提供角色.

I believe this might have been an issue during the installation of SQL 2008 R2. I did specify NETWORK SERVICE, but for some reason it was not provided the role.

这篇关于Server 2012 R2 网站连接问题上的 SQL Server 2008 R2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！