?
Solved

Setting SQL Server 2000 access permissions / System.Data.SqlClient.SqlException

Posted on 2006-05-12
13
Medium Priority
?
575 Views
Last Modified: 2008-01-09
Hi,

I'm getting a "Login failed for user 'yadda'" while stepping through my code.  It breaks when I try to open a connection to the DB.  I've double and triple-checked my connection string (which is initializing, no problem there I don't think) and my permission settings in my DB.

Here's my connection string:

      <appSettings>
            <add key="conn" value="Persist Security Info=False;Initial Catalog=Nurse Education Modules;Data Source=(local);User Id=Nurse;password=nurse;" />
      </appSettings>


In SQL Server, under Logins the Security folder, I created a login called "Nurse".  
I checked SQL Server Authentication and entered a password (as reflected in my connection string).  I also specified the proper DB to be used.  In the "Database Access" tab, I checked 'Permit' beside the relevant DB.
After created the Login, I went to 'Users' in the DB itself.  Under 'Database role membership' I checked 'public', 'db_datareader' and 'db_datawriter'.  Then, I clicked on the Permissions button and ensured that SELECT, INSERT, UPDATE and DELETE were checked for the tables I have.

So far, I'm still running into problems.
Thanks in advance for any help,
J
0
Comment
Question by:tmccrank
12 Comments
 
LVL 9

Expert Comment

by:udayshankar
ID: 16670524
Initial Catalog=Nurse Education Modules

is this the name of the database? with the spaces??
0
 

Author Comment

by:tmccrank
ID: 16670667
Yes.  It's exactly how it's spelled in SQL.
0
 
LVL 3

Expert Comment

by:Titan522
ID: 16670856
Usually you dont have spaces in database names or any names. Try [Nurse Education Modules]
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:tmccrank
ID: 16682944
No... Placing the name of the DB in the connection string in square brackets doesn't help either.
0
 

Author Comment

by:tmccrank
ID: 16686296
Anyone?  Bueller?
0
 
LVL 3

Expert Comment

by:napel25
ID: 16688740
Maybe with double quotes?

Or maybe you can set a default database for the login and skip the database name from the connect string?
0
 

Author Comment

by:tmccrank
ID: 16692520
OK, I figured it out.  However, I don't know why it worked one way and not the other... if anyone could explain it to me I would really appreciate it! (500 points in there too!)

Here's what I changed (these changes finally allowed access to the DB):

1) Went into the SQL Server Properties, Security, and chose mixed-mode (SQL Server and Windows) authentication.
2) Changed my code...
         a) Took <appSettings> out of web.config.
         b) Took out the following code from my DataAccess Class---> Protected conNEM As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("conn"))
         c) Added the following code to my DataAccess Class--->Dim strConnection As String = "data source=(local);uid=Nurse;pwd=nurse;initial catalog=Nurse Education Modules"
    Dim conNEM As New SqlConnection(strConnection)
         d) Changed the code in the DataAccess Class to reflect "conNEM" as the changed SqlConnection variable name.

Worth noting: after I changed to mixed-mode authentication, I tried to run my project WITHOUT changing any of the code, i.e. WITH the <appSettings> in web.config and ---> Protected conNEM As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("conn")) in the DataAccess Class.  So that is my question.... It did not work that way.  Why not??  Any ideas?  I like the idea of having my connection string in web.config, so figuring this out would be great.

Thanks,
J
0
 
LVL 3

Expert Comment

by:napel25
ID: 16697518
The mixed mode is indeed mandatory to make SQL Server authentication work. Otherwise it only accepts Windows authenticated logins.
I'm not a .net developer, I did quit with vb6 and its asp brother.
Can't you set only the connection string in the web.config or global.asa and use that string to open the connection in the code?
0
 

Author Comment

by:tmccrank
ID: 16699636
This has been my problem... the connection string in web.config doesn't work.  If I put it inside my Class, it works; I have no idea why.
0
 
LVL 3

Expert Comment

by:napel25
ID: 16699675
I think you declared the complete connection in web.config. You can also define only the text, the connection string outside the public area.

But indeed, this is a workaround. Not an answer to your question.
0
 

Author Comment

by:tmccrank
ID: 16700614
Sorry napel, I don't quite understand what you mean with 'define only the text, the connection string outside the public area'?
I've seen it done with the key-value pair using <appSettings> in web.config before.... anyway, it's a little frustrating!
j
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 16839965
Closed, 500 points refunded.
Netminder
Site Admin
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

840 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