Creating a database remotely using C#

Question

I'm trying to create a database by remote connecting to a SQL Server Express using Visual Studio 2008. I log onto SQL Server using SQL Authentication. I can do that remotely using SQL Server Management Studio Express no problem by using the following command:

CREATE DATABASE dbname ON (NAME='dbname_dat', FILENAME='C:\folder\dbname.mdf') LOG ON (NAME='dbname_log', FILENAME='C:\folder\dbname.ldf')

However, when I use the same line in my code, I get the following error:

CREATE DATABASE permission denied in database 'master'.

Error number is 262 and class number is 14. My connection string is:

server=servername;database=master;trusted_connection=yes;Pooling=false;

Can anyone help?

Answer 1

This is too obvious, but check your permissions on the server. Are you a sysadmin or a dbcreator?

Also, check your user credentials. Your connection string says trusted_connection=yes but then you say that you are logging in using SQL Server authentication. Which one are you or should you be using?

Answer 2

Well, the error message tells you what is wrong, doesn't it? The user you use to log into via C# doesn't have sufficient permission to execute CREATE statements.

You seem to use a different user to log in via SQL Server Management Studio than to log in via C#. Do you log into the SQL server via Windows Authentication and via SQL Server authentication in C#? Or maybe in Management Studio you have selected a different database in which to execute the sql statement? Check the combobox for the database the script uses.

Answer 3

Try modifying your connection string to something along the lines of this (Note the User and Pwd settings):

tmpConn.ConnectionString = "SERVER = " + DBParam.ServerName + "; DATABASE = master; User ID = sa; Pwd = sa";

The sa account would have the rights to do this command.

Answer 4

I'd suggest that your credentials are not being passed through to the database. If this is a website, this could happen for a number of reasons:

• You don't have impersonation turned on
• You have basic authentication turned on in IIS..

See this article on MSDN about How to Access SQL Server Using Windows Integrated Security for detailed instructions about setting up integrated security, or switch to a fixed SQL Server account.