SQL Server Security Modes and SMS

SQL Server Security Modes and SMS

You can set up SQL Server with one of three security modes: standard, integrated, or mixed. The method you use to enter the SQL Server login determines the security mode you use. But whichever security mode you use, you must still create mappings to database users.Standard security requires a user to manually supply a login each time that user establishes a connection to the SQL Server. In this mode, SQL Server handles all security. Using the standard security mode requires no specific preparation other than creating the necessary login accounts.Integrated security relieves a user of the requirement to log in separately to SQL Server. Instead, SQL Server uses the NT username and password to establish a session. For a user, integrated security is the most transparent security mode. However, the integrated security mode requires more planning and preparation for an administrator than either of the other security modes. SQL Server doesn't automatically use all NT accounts. You must use the SQL Security Manager to grant NT users access to SQL Server. Thus, in a multidomain environment, your SQL Server system must have access to all the domain accounts you want to use. Integrated security also requires SQL Server to use specific network protocols (i.e., Named Pipes or Multiprotocol). Make sure that all client computers that access the SQL Server database can address these protocols.Mixed security is a combination of standard and integrated security. SQL Server first tries to authenticate users through integrated security. If SQL Server can't authenticate users through their NT credentials, SQL Server tries to authenticate those users through standard security. You can use the mixed security mode as an alternative to the integrated security mode if your network doesn't meet the necessary requirements for integrated security.If you don't have SQL Server experience, use standard security because it's the easiest mode to set up and administer. Integrated security is easier for a user to use; however, this mode requires more preparation and SQL Server knowledge to set up. In addition, standard security lets users log on to the database with different security levels. For example, if you create a Visual Basic (VB) or Access custom application to query the SMS inventory, you should connect users to the database using an account that has View permissions only. After all, unless you make all forms read only (which might defeat the purpose of a search form), users can edit the SMS site data directly if they use the systems administrator login to access the SMS database. Using an account with restricted access is 100 percent secure and doesn't adversely affect the application.Having a thorough understanding of how to administer SQL Server will help you keep your SMS environment safe and well tuned. If your company doesn't have a dedicated database administrator, consider signing up for training. You'll be glad you did.