Solved

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

Posted on 2006-11-05
14
593 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
0
Comment
Question by:stevejebson
  • 7
  • 7
14 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17876048
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
0
 
LVL 1

Author Comment

by:stevejebson
ID: 17876066
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
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17876069
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.
0
 
LVL 1

Author Comment

by:stevejebson
ID: 17877110
ok, back now finally !

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

Expert Comment

by:Nightman
ID: 17877277
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.
0
 
LVL 1

Author Comment

by:stevejebson
ID: 17877303
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 ?

0
 
LVL 29

Expert Comment

by:Nightman
ID: 17877323
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)
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:stevejebson
ID: 17877341
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 ?
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17877359
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.
0
 
LVL 1

Author Comment

by:stevejebson
ID: 17877385
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)
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17877494
OK, let's work this backwards

Are you using ASP.NET or classic ASP?
0
 
LVL 1

Author Comment

by:stevejebson
ID: 17877707
...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 ??
0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17879164
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)
0
 
LVL 1

Author Comment

by:stevejebson
ID: 17883120
Thanks for all the help Nightman.

Steve
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now