• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

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

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
tmccrank
Asked:
tmccrank
1 Solution
 
udayshankarCommented:
Initial Catalog=Nurse Education Modules

is this the name of the database? with the spaces??
0
 
tmccrankAuthor Commented:
Yes.  It's exactly how it's spelled in SQL.
0
 
Titan522Commented:
Usually you dont have spaces in database names or any names. Try [Nurse Education Modules]
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tmccrankAuthor Commented:
No... Placing the name of the DB in the connection string in square brackets doesn't help either.
0
 
tmccrankAuthor Commented:
Anyone?  Bueller?
0
 
napel25Commented:
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
 
tmccrankAuthor Commented:
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
 
napel25Commented:
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
 
tmccrankAuthor Commented:
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
 
napel25Commented:
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
 
tmccrankAuthor Commented:
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
 
NetminderCommented:
Closed, 500 points refunded.
Netminder
Site Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now