Jump to content

Microsoft SQL Server/Installing Instances

From Wikibooks, open books for an open world

Decide on Security and collation

SQL Server Agent and SQL Server run as Windows Services.

They run in a context of a user account. Determining which account is an important decision.

Questions:

  • Should use use a separate account for the SQL Server service and the SQL Server Agent service, or should you use the same account for both?
  • Should you use a built-in system account or a domain user account?

Remember: The SQL Server jobs require to interact with the SQL Server Agent service. When the SQL Server Agent service must interact with different servers, DBAs create different accounts for these two services to avoid giving the SQL Server service more permission than it needs.

Build-in System account or Domain user account

You can choose between: Network service account, local system account, or a dedicated domain user account.

The Network service account is a special built-in system account that is similar to authenticated user accounts. This account has the same level of access to system resources and objects as members of the Users group. Services that run under this account will use the credentials of the computer account to access network resources. Not recommended to use.

The local system account is a Windows OS account that has full adminstrativve rights on the local computer but has no network rights. You can use this account for development or testing of servers that you do not integrate with other server applications or to interact with any network resources. Not recommended.

Recommended: Create and use one or two dedicated domain user accounts for the SQL Server and SQL Server Agent services.


Authentication mode

[edit | edit source]

Windows and Mixed mode. Default is Windows: Only users that have previously authenticated to the Windows OS can connect to the SQL Server 2005 instance.

Mixed is recommended for Legacy.

Determining Collation Setting

[edit | edit source]

You define the default collation for a SQL Server at installation. The SQL Server uses the collation setting to determine how non-Unicode character data is stored and how to sort and compare Unicode and non-Unicode data.

Default will be selected of Windows.