question

BobbyP-1695 avatar image
0 Votes"
BobbyP-1695 asked cooldadtx commented

SQL Server Service Account

Just wondering if anyone has some good suggestions and maybe Standards for defining a SQL Server Service Account and its name?

Like should it include "Service" so that it stands out?

I know it is "Shop" specific...but just looking for some good suggestions.

Thanks for your review and am hopeful for a reply.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BobbyP-1695,

Any update for this thread? Did you check the replies from cooldatx and Andreas Baumgarten? If the response helped, do "Accept Answer". By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
cooldadtx avatar image
0 Votes"
cooldadtx answered cooldadtx commented

The recommendation is to not do that anymore. As of SQL 2016 (or perhaps as early as 2012) the setup is going to default to the recommended and builtin accounts for all its services. You shouldn't really be creating dedicated accounts anymore as you shouldn't need them. There are a few exceptions for advanced scenarios like failover clusters from my understanding. So my naming recommendation is "don't".

In previous companies that either had old SQL servers that pre-date this or were stuck in their SOP-style world of mandating accounts then they would put "service" in the name somewhere but often shortened it to something like sql-svc-dev or something. The problem is, of course, that if you have more than 1 SQL server instance then you should really be creating separate accounts for each one as you don't want to accidentally give 1 service account access to another server's stuff. That means you run into the questionable naming convention of sql-svc-myserverinstance-dev or something like that. Using the recommended accounts resolves these issues.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How does one create an OLE DB Connection in SSIS with some "builtin" account??

0 Votes 0 ·

Service accounts are what you run services under. Setting up a DB connection would use whatever user account you have configured in the DB. Therefore I don't understand your question. Creating a DB connection would simply use one of the SQL db user accounts (or Win auth if you are using that). This is completely unrelated to the service account under which a service would run as that account needs to have permissions on the server SQL is running on but doesn't necessarily have rights to the DBs hosted by that server.

But for the more general question of how you simply use the BUILTIN\NETWORK SERVICE or whatever account.

0 Votes 0 ·
AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered AndreasBaumgarten edited

Hi @BobbyP-1695 ,

at all of your customers service accounts could be identified by some kind of prefix. For instance
svc-sqlservice -> generic SQL service account
svc-CRMsqlservice -> SQL service account from CRM application
or if "lazy" ;-)
svc-sql
svc-CRMsql

Instead of svc you will find similar prefixes depending on the naming conventions like s , sv, srv , ....
The separator varies from - over _ to # depending on customer's naming conventions.


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cthivierge avatar image
0 Votes"
cthivierge answered

I think that the name should also describe the service / environment with a incremental number

Personally, i do prefer to add something that identify the service account for the environment. For example, development should look like... svc-sql-dev01... It will be easier to identify the service account.

I would also look to implement Group Managed Service Account (gMSA). Compare to a standard account, you don't have to manage the password for this account. It change automatically so it's more secure. Also, you cannot use this account to login on the server and you can still apply service principal name to the account if you need Kerberos authentication

https://docs.microsoft.com/en-us/windows-server/security/group-managed-service-accounts/group-managed-service-accounts-overview

hth

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.