May 1, 2013 at 9:11 am
Hello,
I'm developing an ASP.net website which will interact with an SQL Server 2012 database via parameterised queries. I've got the stored procedures and ASP.net covered, but I'm stuck on what's required on the user/login side in SQL Server 2012. Now I've worked with this setup once before but didn't have to worry about that side of things as a DBA colleague took care of it while I focussed purely on the coding. Unemployed and no DBA now though.
From memory I thought I needed to create a user with execute permissions so I attempted that(via security > users> new user) but was prompted for "user type", "login name" and a "default schema". I'm really not sure what to choose for those. I tried googling, but ended up horribly chasing my tail (for a nice change) ... and here I am.
I have half a feeling that "login name" is not required if the database is set to "windows authentication". If so I'd guess I need to select "SQL user without login", and just create a user with execute permissions. But that's a horrible amount of compound guesswork and I really don't want to blindly fudge this crucial part of the website.
So I'd appreciate some advice on that if anyone knows.
In summary I would like to know whether I need to create a login, a user, both, and, if possible, what settings to choose in the create dialogues.
Thank you very much,
David.
May 1, 2013 at 9:38 am
If you are on a domain, a normal way to handle this is to have the Web server service account connect to the database server.
1. Create a role in the database that will be used to grant permissions.
2. Grant execute permissions to that role for all stored procedures it will use.
3. Create a login for the Web server account and add it as a user in the database.
4. Add the web server login to the role in the database.
5. If there is more than one login needed, repeat steps 3 and 4.
May 1, 2013 at 9:56 am
Michael Valentine Jones (5/1/2013)
If you are on a domain, a normal way to handle this is to have the Web server service account connect to the database server.1. Create a role in the database that will be used to grant permissions.
2. Grant execute permissions to that role for all stored procedures it will use.
3. Create a login for the Web server account and add it as a user in the database.
4. Add the web server login to the role in the database.
5. If there is more than one login needed, repeat steps 3 and 4.
Thank you for the reply Michael.
I'm afraid there's a lot of gaps in my experience so I'm not familiar with all the terminology you've used there.
I did start writing questions, but there were just so many. So before I do that - are you aware of ant step-by-step guides explaining how to achieve what you've outlined above?
Thanks,
David.
May 1, 2013 at 10:08 am
here's an example i adapted from another one I've used int eh forums here:
-- we need a login for the Web To Use:
create login WebUser with password = 'NotTheRealPassword'
--pick the right db!
USE [WHATEVER]
--now we need a matching USER for the LOGIN in the "right" database
Create USER [WebUser] FOR LOGIN [WebUser]
--now that the user exists, create the role we have planned for it:
--Create a role with only EXECUTE permissions on ALL procedures:
CREATE ROLE [ExecuteForWeb]
--if ALL procedures, this is good.
--if it's NOT, don't run this and use the commented example, where you substitute and expand the "right" procs
GRANT EXECUTE TO [ExecuteForWeb]
/*
GRANT EXECUTE On pr_InsertCustomers TO [ExecuteForWeb]
GRANT EXECUTE On pr_SelectCustomers TO [ExecuteForWeb]
GRANT EXECUTE On pr_UpdateCustomers TO [ExecuteForWeb]
GRANT EXECUTE On pr_InsertAddress TO [ExecuteForWeb]
GRANT EXECUTE On pr_SelectAddress TO [ExecuteForWeb]
GRANT EXECUTE On pr_UpdateAddress TO [ExecuteForWeb]
-repeat for specific views or tables, if appropriate
*/
--now that the role exists, lets add our user to that role:
EXEC sp_addrolemember N'ExecuteForWeb', N'WebUser'
--test:
EXECUTE AS USER='WebUser'
--who am i? I'm Webuser!
select suser_name()
--do stuff
--SELECT * FROM dbo.Customers --Received error due to permissions
--EXEC pr_SelectCustomers() --Works
--change back into superman
REVERT;
--clean up after ourselves
/*
DROP ROLE [ExecuteForWeb]
DROP USER [WebUser]
DROP LOGIN [WebUser]
*/
Lowell
May 1, 2013 at 10:16 am
a slight modification if you are using windows authentication:
-- we need a login for the Web To Use:
CREATE LOGIN [DEV223\ASPNET] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
--pick the right db!
USE [WHATEVER]
--now we need a matching USER for the LOGIN in the "right" database
Create USER [DEV223\ASPNET] FOR LOGIN [DEV223\ASPNET]
--substitute our ASPNET user for our SQL User we had int eh first example
Lowell
May 1, 2013 at 11:19 am
Thank you for the reply Lowell.
I was able to tally up the SQL in that script with Michael's instructions above. So much appreciated.
I've just hit a bump - not entirely sure if I should be using Windows authentication or SQL Server authentication. I'm using Windows authentication on my local dev copy but just noticed the last website I worked on was set to use SQL Server authentication.
So I think I need to ascertain which is appropriate before I continue.
Anyway I think that's a question for a different forum!
Thanks for all the help,
David.
May 3, 2013 at 3:27 pm
In case anyone else arrives here with the same original question I had - here's an update based on feedback here and in another forum:
Generally speaking, you can use either SQL Server authentication or Windows authentication to connect to an SQL server database from a website.
Windows authentication is considered best practice as credentials aren't hard-coded into the connection string.
However if the web/database servers are on different domains, separated by a firewall, or the database server runs on Unix, then Windows authentication isn't an option so SQL Server authentication must be used instead.
So if you want to use Windows authentication:
1) As per Lowell's post, do the following in SQL Server
-- we need a login for the Web To Use:
CREATE LOGIN [DEV223\ASPNET] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
--pick the right db!
USE [WHATEVER]
--now we need a matching USER for the LOGIN in the "right" database
Create USER [DEV223\ASPNET] FOR LOGIN [DEV223\ASPNET]
--now that the user exists, create the role we have planned for it:
--Create a role with only EXECUTE permissions on ALL procedures:
CREATE ROLE [ExecuteForWeb]
--if ALL procedures, this is good.
--if it's NOT, don't run this and use the commented example, where you substitute and expand the "right" procs
GRANT EXECUTE TO [ExecuteForWeb]
/*
GRANT EXECUTE On pr_InsertCustomers TO [ExecuteForWeb]
GRANT EXECUTE On pr_SelectCustomers TO [ExecuteForWeb]
GRANT EXECUTE On pr_UpdateCustomers TO [ExecuteForWeb]
GRANT EXECUTE On pr_InsertAddress TO [ExecuteForWeb]
GRANT EXECUTE On pr_SelectAddress TO [ExecuteForWeb]
GRANT EXECUTE On pr_UpdateAddress TO [ExecuteForWeb]
-repeat for specific views or tables, if appropriate
*/
--now that the role exists, lets add our user to that role:
EXEC sp_addrolemember N'ExecuteForWeb', N'WebUser'
--test:
EXECUTE AS USER='WebUser'
--who am i? I'm Webuser!
select suser_name()
--do stuff
--SELECT * FROM dbo.Customers --Received error due to permissions
--EXEC pr_SelectCustomers() --Works
--change back into superman
REVERT;
--clean up after ourselves
/*
DROP ROLE [ExecuteForWeb]
DROP USER [WebUser]
DROP LOGIN [WebUser]
*/
2) Amend your connection string(in web.config) to contain "Integrated Security=True" and no "user ID" or "password". Here's an example:
<add name="your_connection_string_name" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\your_database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
{replace your_connection_string_name with whatever you want to call it}
{replace your _database.mdf with the name of your database file}
{apparently "Integreated Security=SSPI" is required when using OLEDB or an exception is thrown}
Hope this saves someone the painful wild goose chase I've experienced!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply