We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

SQL Server Express connection problems - CREATE DATABASE permission denied in database 'master'

Steve Jebson
Steve Jebson asked
on
Medium Priority
633 Views
Last Modified: 2007-12-19
Hi,

I've been trying to get connected to sql server database for the last 2 days now and i'm going round the bend !

i checked previous posts and found the http://www.connectionstrings.com/ site and i have created a connection string as below but get the following error !

i'm sure this shouldn't be as difficult as it's proving to be !

the error is....

[Microsoft][SQL Native Client][SQL Server]CREATE DATABASE permission denied in database 'master'.

and my connection string is...

<%
strConnection = "Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|jellydb_data.mdf;Database=jellymould;Trusted_Connection=Yes;"
%>


if i use a connection string of...

<%
strConnection =  "Driver={SQL Native Client};Server=TREVOR\SQLEXPRESS;Database=jellymould;Trusted_Connection=yes;"
%>


i get an error of....

      [Microsoft][SQL Native Client][SQL Server]Cannot open database "jellymould" requested by the login. The login failed.

thanks in advance

Steve
Comment
Watch Question

Commented:
Hi Steve

It looks pretty straightforward to me - the account that you are using to connect does not have access to the server.

Usually this is becuase you are running as IUSR_MACHINENAME (or in this case probably IUSR_TREVOR) - this account does NOT have access to any network or database resources by default.

Your options are to run with windows authentication 9then it will connect in the security context of the user) or to create an SQL login (DON'T USE SA ACCOUNT) and connect with that account
Steve JebsonDeveloper

Author

Commented:
i've now got to go out for a couple of hours but i really need to come back to this, i think i'm going to need step by step on this, i think i want to run with windows authentication 'cos i'm only running this on my local machine but i'll do whatever works ;-)

sorry to dash off like this, wife/family etc...

hope to see you back in a couple of hours...

Steve

Commented:
No problem - my wife and daughter will be back in a couple of hours ;) - post when you can, and I'll check up every now and then.
Steve JebsonDeveloper

Author

Commented:
ok, back now finally !

sooo.. how do i set the IUSR_TREVOR account so ican get this connection string working ??

Commented:
I would advise against giving that account permissions.
If you know that all uses connecting to the site should have database access (and therefore you aren't worried about having to handle permissions exceptions, set the web site up to run with intergrated windows authentication.

Right click the web site in IIS, select directory security, edit the anonymous access and authentication methods and turn off anonymous access. Only authenticated windows users will now have access (so if the account ahs permission to access the database you will be fine). Alternatively, you can create a local user that has access to the database and run with anonymous access, and use this account instead of the IUSR_ account.

OR you could create a SQL login, and use this account in the connection string.
Steve JebsonDeveloper

Author

Commented:
Ok, I tried turning off the Annonymous Access and when i tried to load the page it prompted me for User and password ??? not sure what to enter here ?

maybe it would be easier to create the SQL login ?

Commented:
It should be authenticating against your windows password - not sure why it isn't.

To create the SQL login, go to security-->logins under SQL Management Studio. Right click logins and select new login.

Create the account, give it a secure password and make sure that it is created for SQL authentication. If you want this role to be able to create databases, add it to the dbcreator server role.

Then use this account to connect with your connection string (there is an example at www.connectionstrings.com)
Steve JebsonDeveloper

Author

Commented:
ok, when i tray and create the User against this DB it ask me for a UserName and then for the Login Name, when i browse for Login types i'm presented with a list i.e.

[BUILTIN\Administrators]
[BUILTIN\Users]
[NT AUTHORITY\NETWORK SERVICE]
[NT AUTHORITY\SYSTEM]
[sa]
[TREVOR\ASPNET]
[TREVOR\SQLServer2005MS....


which one should i chose ?

Commented:
You are creating an account with windows authentication, so it is prompting you for a windows account. Try selecting SQL authentication (just below login name) first - this will allow you to create a SQL login.

If this option is greyed out, it is because the SQL setup has only enabled windows authentication. To change this, right click on the server properties in management studio, select security and choose SQL Server and Windows Authentication mode.

You may need to restart SQL Server after this change. If you have not already specified a password for the sa (system administrator) account, you may be prompted to do so now. Make sure that it is a strong password, and DON'T forget it.
Steve JebsonDeveloper

Author

Commented:
ok, i've turned on SQL Server and Windows Authentication mode, stopped and restarted the server but the only options below the Login Name are : Certificate Name, Key Name and Without Login (all greyed out)

Commented:
OK, let's work this backwards

Are you using ASP.NET or classic ASP?
Steve JebsonDeveloper

Author

Commented:
...sorry, Planet Earth and fag break !

both actually, i've got asp.net 2.0 projects running but i was using a classic asp set og pages against a sql server db for the 1st time, is this all a bit screwey ??
Commented:
Not really as screwy as you think - many of us have older legacy systems running side-by-side with .NET.

OK - in the interim, set up your windows account to run IIS instead of IUSR_ - this will at least get you going in the mean time until you can work through the security model on your system. Remember though that if you password expires, you will have to change it in the IIS settings (or else your account may get locked out).

I would recommend changing this to an SQL login as soon as you are able (although why you need a cert name it beyond me - I have never seen that before)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Steve JebsonDeveloper

Author

Commented:
Thanks for all the help Nightman.

Steve
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.