1

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.

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.

My con string: 

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.

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.

Here is the error:

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.

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

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.)

UPDATE: Uninstalled and reinstalled SQL Server and SSMS.

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.

I believe the specific error message relating to

CREATE DATABASE permission denied in database 'master'

is the issue.

I have never seen this error before.

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.

htm11h
  • 1,739
  • 8
  • 47
  • 104
  • Wait, where's Server 2003 coming from? Previously you said they were all installing on [Windows Server 2008 R2](http://stackoverflow.com/questions/29518829/security-principals-missing-in-sql-server-2008-r2)? As I said before, there was [a significant change between how service accounts work](https://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx#New_Accounts) after Windows Server 2008 R2. Server 2003 is significantly different, too. – Bacon Bits Apr 08 '15 at 18:21
  • Does your application try to create the database if it can't access it? Did you actually copy the database from an old system to a new system? Did you verify that the [server logins and database users are still correctly mapped](https://msdn.microsoft.com/en-us/library/ms175475(v=sql.105).aspx)? – Bacon Bits Apr 08 '15 at 18:23
  • So I have SQLSERVER 2008 R2 running on Server 2003, 2008 and now 2012. This database is in the app_Data folder of a web site. I have various versions of IIS running on each server. I have been able to copy the database to any app_data folder and access the DB via the web site logon screen. Server 2003 is the current prod and dev environment 2 systems. Tested the migration on 2008 and decided to finally migrate to 2012. The dev 2012 server has everything up and running perfectly. – htm11h Apr 08 '15 at 18:40
  • I do not create a master db with any code. Or an other database for that matter. I simply copied the myDB.mdf to each app_data folder. – htm11h Apr 08 '15 at 18:42
  • All user accounts appear to be correctly mapped. – htm11h Apr 08 '15 at 18:42

2 Answers2

2

Your connection string says AttachDbFilename=|DataDirectory|\myDB.mdf. Well, your path must have changed in one place (SQL Server) and not the other (app configuration). AttachDbFilename will try to create a new DB if it can't find that file. That's probably your error.

As an aside, Windows Server 2003, Windows Server 2003 R2, Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, and Windows Server 2012 R2 all have differences, especially with security and in particular with how SQL Server behaves, but you keep using them interchangeably. That makes it very confusing to tell what's going on. Also, your connection string says Data Source=.\SQLEXPRESS. Is this SQL Server Express? Because that has important differences with most other editions of SQL Server, too. More recent editions are very similar to Standard edition, but the older the edition, the more quirks it has. They're generally considered different beasts.

Community
  • 1
  • 1
Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
1

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.

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.

htm11h
  • 1,739
  • 8
  • 47
  • 104