3

I am using SQL Server 2008 R2 and am facing a problem. The application that I have developed needs to be tested at client's site which is at different locality. So I plan to configure the client's machine once and then for any changes related to application I will just distribute a asp.net mvc deployment package which client can deploy on IIS. For that, I need to provide my asp.net application ability to drop and create database (through codefirst entity framework). In the present configuration, I am facing permission issue related to dropping the database. The Application somehow is unable to drop the database. Here is summary of IIS and SQL Server configuration that I am using.

For IIS, I have set the Application Pool Identity to "Local Service" as per the standard practice. The connection string in asp.net web.config file is given below.

connectionString="Server=.\SQLEXPRESS;Database=SomeDatabase;Trusted_Connection=true;User Id=someuser;Password=somepassword" />

For SQL Server Service, I have provided "Local Service" as log on, again providing the minimum access here for the service. For SQL Server Instance Logins I have defined the user and password and given complete authority ("sysadmin") role.

With this configuration in place I was expecting my IIS application to connect using the user and password created above and have the ability to drop and create the SQL Server database. But I am getting permission denied for Dropping Database. The Exception is given below.

System.Data.SqlClient.SqlException (0x80131904): Cannot drop the database 'SomeDatabase', because it does not exist or you do not have permission.

I have checked that the database exists so it boils down to permissions. Am I missing out some configuration ?

Improve this question
1
  • If you connect to the database, not through your application, but through the commandline tools as one of the users, can you drop the database? Commented Sep 20, 2011 at 2:15

3 Answers 3

Reset to default
2

To be clear, your connection string is a bit malformed, and may not be behaving as you expect.

When you specify Integrated Security=true in your connection string, then Windows Authentication occurs. Any user id= attribute in the connection string will be ignored.

Switch to SQL Server authentication mode by dropping your Integrated Security=true attribute.

   Server=.\SQLEXPRESS;Database=SomeDatabase;
   User Id=someuser;Password=somepassword;

Further, the DROP DATABASE command can be executed by the database owner, a user who's a member of the db_owner role, or a user in a server admin role.

Add the database user someuser to the db_owner role.

 EXEC sp_addrolemember 'db_owner', 'SomeUser';

Alternatively, if you determine that the account above should NOT be in this role (i.e. restrictive security environment, policies, etc), consider creating and using another account just for this purpose. This would likely mean maintaining another connection string. If the separation of users/roles is important enough for you, perhaps this second option will work.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I think when I gave sysadmin (instance level) role to the user, it percolates down to database and the db_owner (as well as all other roles at database and schema level) are granted to the user. So I see no point allowing the db_owner role to the application. Also, if the database is dropped (by my application) then the db_owner role is deleted too, so it would serve no purpose. Again, I am just a beginner so not very sure about what I have commented.
@nathan, I know but I couldn't think of another option. As I said earlier, if I give the access db_owner at database level it would work well till the database is dropped. After my database is dropped, how would the application be able to recreate the database, as the original database is gone as well as the db_owner rights that were available to the application.
2

I think that the real account being used on the Sql connection is the 'Local Service' because you defined Trusted_Connection=True in the connection string. Try to remove it and see what happens. If I'm not wrong, this parameter will make use of a Windows Integrated Account, the Local Service in your case.

Improve this answer

Comments

0

While specifying credentials in the connection string, you either need to omit Trusted_Connection part or set it to False

Data Source =myServerAddress; Initial Catalog =myDataBase; User Id =myUsername; Password =myPassword;

OR

Server =myServerAddress; Database =myDataBase; User ID =myUsername; Password =myPassword; Trusted_Connection =False;

Refer http://connectionstrings.com/sql-server-2008 for more details.

Improve this answer

3 Comments

If I set Trusted_Connection to false or omit it, I get a different exception(System.Data.ProviderIncompatibleException: The provider did not return a ProviderManifestToken string.), and the Login fails. Any ideas why this exception ?
In that case, you have got the connection string little wrong. You need to add 1) context 2) provider name in your connection string . Refer this quick blog for details - mattfrear.com/2011/01/01/…
Suhas, @Campbell, Setting Trusted_Connection to false and setting authentication mode to SQL Server worked out. I wonder know how I missed that. Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.