locked
sql 2008 r2 security setiing up windows authentication for entity framework silverlight etc RRS feed

  • Question

  • I am trying to set up windows authentication correctly eventually over the internet for entity framework etc. My problem is this I got hit over the internet with an injection attack so this is question is more about best practices Please be aware I am trying to use entity framework silverlight etc

    My understanding and Please correct me if i am wrong if you are going to use

    My first question is This Can you limit the system administrator's role?

    What is the best way to do so? 

    Please continue reading I have more questions....     

      

     

    • Edited by wm_s Wednesday, June 16, 2010 8:33 PM
    Wednesday, June 16, 2010 8:10 PM

Answers

  • You cannot limit the permissions of the SQL Server system administrators. (Members of the SQL Server sysadmin fixed server role.)

    I think you are mixing up Windows Authentication and SQL Server Authenticaion. To use Windows Authentication from your client, the client must be a member of the domain. See the Books Online topic Choosing an Authentication Mode http://msdn.microsoft.com/en-us/library/ms144284.aspx If clients on the internet are connecting directly to your SQL Server, then you need to use SQL Server Authentication mode for them. And  your steps are correct. They get individual logins or one big group login. You map that login to a Database User, which has almost no permission. They can just execute your stored procedures. You should probably disable the SA account (a SQL Server login), because everyone and their dog will try to hack it. You as an administrator should use a Windows login. (SQL Server Authentication can be turned off, but Windows Authentication is always present.)


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, June 17, 2010 3:26 PM

All replies

  • My second question is concerning  creating a sql dba for creating tables etc . My understanding in windows authentication you need to create a  sql windows login  I looked at the following video. Can I use object explorer or is it safer choosing a different way....  

       http://www.asp.net/sql-server/videos/understanding-security-and-network-connectivity

    1 Create a sql server login for your application account

    2 Map the login to a database user assign schemas, roles etc    

    Grant limited permissions on stored procedures

    I only am planning two users so far system admin and sql dba

    Is this the best way to handle this...I just want to do this the right way and yes eventually I want this over the internet, silverlight wcf

    entity framwork etc

    Thanks   

    Wednesday, June 16, 2010 8:24 PM
  • You cannot limit the permissions of the SQL Server system administrators. (Members of the SQL Server sysadmin fixed server role.)

    I think you are mixing up Windows Authentication and SQL Server Authenticaion. To use Windows Authentication from your client, the client must be a member of the domain. See the Books Online topic Choosing an Authentication Mode http://msdn.microsoft.com/en-us/library/ms144284.aspx If clients on the internet are connecting directly to your SQL Server, then you need to use SQL Server Authentication mode for them. And  your steps are correct. They get individual logins or one big group login. You map that login to a Database User, which has almost no permission. They can just execute your stored procedures. You should probably disable the SA account (a SQL Server login), because everyone and their dog will try to hack it. You as an administrator should use a Windows login. (SQL Server Authentication can be turned off, but Windows Authentication is always present.)


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, June 17, 2010 3:26 PM