Solved

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

Posted on 2006-11-05
14
603 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Questions about INCLUDE FILES 2 46
Hide Table in merge 3 53
Debugging Html 8 44
StoredProcedure to JSON query faulty syntax 2 45
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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