tmccrank
asked on
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
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
ASKER
Yes. It's exactly how it's spelled in SQL.
Usually you dont have spaces in database names or any names. Try [Nurse Education Modules]
ASKER
No... Placing the name of the DB in the connection string in square brackets doesn't help either.
ASKER
Anyone? Bueller?
Maybe with double quotes?
Or maybe you can set a default database for the login and skip the database name from the connect string?
Or maybe you can set a default database for the login and skip the database name from the connect string?
ASKER
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.Confi
c) Added the following code to my DataAccess Class--->Dim strConnection As String = "data source=(local);uid=Nurse;p
Dim conNEM As New SqlConnection(strConnectio
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.Confi
Thanks,
J
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.Confi
c) Added the following code to my DataAccess Class--->Dim strConnection As String = "data source=(local);uid=Nurse;p
Dim conNEM As New SqlConnection(strConnectio
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.Confi
Thanks,
J
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?
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?
ASKER
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.
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.
But indeed, this is a workaround. Not an answer to your question.
ASKER
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
I've seen it done with the key-value pair using <appSettings> in web.config before.... anyway, it's a little frustrating!
j
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
is this the name of the database? with the spaces??