locked
Windows Authentication RRS feed

  • Question

  •  

    Ok so this is a VERY basic question but.... We are switching our authentication method from mixed to windows and I was wondering if anyone can point me to the best doc to get up to speed. Specifically we are using express and deploy this to our customers who DO NOT have inhouse IT staff. Our front end is coded in .NET / WPF.

     

    What is the accepted approach on creating user in SQL Server? Is this supposed to be a manual process?

    Does the password have to match that of windows? Or just the user name?

     

     

    Wednesday, September 10, 2008 6:28 AM

All replies

  • Mixed mode authentication uses both Windows authentication, which uses active directory or local users and groups, and sql server authentication.

     

    Windows authentication for sql server requires that you add the users or groups from an active directory domain or the local workstation into sql server.  No password is suuplied as the users are not truly authenticated in sql sever, user/group name for log ins must match active directory.  Once the user logs into Windows using their username and password and connects to SQL Server then their user and groups are presented to SQL which are checked against system tables to ascertain if they have a login in SQL and if so the permissions available to them.

     

    Mixed mode authentication the username and password are created and maintained in SQL Server.  The user supplies their username and password to sql and sql authenticates the user based on this.  Their permissions are then assigned based on the user within sql.

     

    Windows Integrated authentication is the Best Practice as it provides a more seecure environment, but can be prohibitive based on your environment, if you have users or workstations that must have access to sql, but do not have domain credentials.

     

    Here are some links in reference to authentication:

    http://msdn.microsoft.com/en-us/library/ms144284.aspx

    http://msdn.microsoft.com/en-us/library/aa176599.aspx

    http://msdn.microsoft.com/en-us/library/aa905171(SQL.80).aspx

     

    If you are looking specifically creating a security for an application connection then look at application role's:

    http://msdn.microsoft.com/en-us/library/ms190998.aspx

    http://msdn.microsoft.com/en-us/library/ms181491.aspx

     

    Hope this helps

     

    Wednesday, September 10, 2008 9:56 AM
  • We are going to be using the new FILESTREAM type in 2008. If we utilize the Application Role approach will the security validate appropriately in the OpenFileStream API call?

     

    Wednesday, September 10, 2008 4:44 PM
  • For filestrean I would recommend using Integrated.  Although th efilestrean uses the security context of the logon account of the sql server service or sql server agent if it is a scheduled job.

     

     

    Wednesday, September 10, 2008 5:05 PM
  • It is just the username. However, if you're using a .net Web Based front-end, you'll have to use impersonation to communicate with the server. In this event, you would add the NT Login's to the IIS accounts as well as SQL Server. If you're using stand-alone app's, the identity token will be carried to the application via Windows Authentication.

     

    A.D.T.

     

    Wednesday, September 10, 2008 5:20 PM