Post reply

Security Logins Vs Database Security Users

  • September 24, 2008 at 2:24 am

    #193571

    Hi. I have been working on a intranet site and during testing have been using an existing SQL account within the connection string. Now I would like to improve security and was hoping for some advice.

    I have created AD account (lets call it CString) + AD Security Group to which I have added user CString.

    Now I want to allow that user to execute queries against a particular database in order to return data to a gridview.

    Can you please advise when creating a new Login:

    1) Should I use Windows Authentication (i.e. my AD Security Group) or SQL Server Authentication

    2) Security Logins: User Mapping - Database role membership. Should I apply only 'public' role

    3) Database Security Users: When creating a Database User what Database Role should I assign to allow minimal permissions but sufficient to execute the queries?

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • September 24, 2008 at 3:49 am

    #876197

    Hello,

    To answer your questions:-

    1) Windows Integrated Security is more secure e.g. passwords are not saved in connection strings.

    2+3) If you want to specify access to only certain tables then consider creating a custom DB Role. For read or write access to all tables then the built in db_datareader and db_datawriter roles are fine.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • September 24, 2008 at 4:34 am

    #876212

    Thanks for taking time to reply.

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • September 24, 2008 at 5:15 am

    #876233

    Hi John. I have attempted to add a new connection string for a GridView that returns product details.

    I successfully tested the connection on my local machine when it was created. When I tried to preview the page in a browser I received error:

    Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'DOMAIN\WEB-SERVER$'.

    ASP.NET on the web server has the following entry in the connection string manager.

    Data Source=MY-SERVER\;Initial Catalog=DB1;Integrated Security=True

    On SQL I have a Login for group 'MyUsers' with a user mapping public. On the Database Security I have not defined any database roles.

    When I created the connection string within MS Visual Web Developer the add connection wizard defaults to Use Windows Authentication. However I am unable to specify an account?

    Any ideas as to what I need to do?

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • September 24, 2008 at 5:31 am

    #876246

    Hello Phil,

    You will need to add a SQL Server Login for 'DOMAIN\WEB-SERVER$'' or add it to an appropriate Windows Group that already has a Login on the SQL Server. It must be assumed that the application uses (i.e. impersonates) this account for access to resources.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • September 24, 2008 at 5:55 am

    #876264

    Hi once again John. I am making progress (different error)!

    Error:

    The EXECUTE permission was denied on the object 'sp_product_search', database 'MyDatabase', schema 'dbo'

    sp_product_search being the stored procedure that returns the search results for the product code entered.

    I added the computer object referenced in the initial error to the AD security group. I created a Login for this security group (public). I gave the security group 'db_datareader' role on the database.

    Any thoughts?

    Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • September 24, 2008 at 6:04 am

    #876266

    John I think I have it. I have explicity granted 'Execute' permissions on the stored procedure for the AD Security Group. I am able to run the web page as expected.

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • September 24, 2008 at 6:07 am

    #876271

    Hello Phil,

    That's right, if you are using SPs (as opposed to Selects directly on Tables or Views) then you need to grant execute permission on the SPs.

    Personally I would create a custom DB Role, add the DB Users to this Role and then run "Grant Execute MySPName To MyDBRole" statements for each SP they need to execute. It's a good way of grouping permissions and makes admin easier.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • September 24, 2008 at 7:25 am

    #876333

    Hi John. What would the correct syntax be to grant execute permissions to a single stored procedure?

    Stored Procedure: sp_product_search

    DB Role: db_ExecuteStoredProcs

    Thanks,

    Phil.

    Update: Got it........

    GRANT EXECUTE ON sp_product_search TO db_ExecuteStoredProcs

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply