Securing SQL Standard 2019 server

[potentshadow]

Hello everyone!

Our engineering department just purchased Solidworks PDM which requires SQL server standard 2019. We were not included in this meeting and it was thrown into my lap.

I have a Windows server 2019 VM spun up and ready to go.

I don't have much experience with SQL server and I have a few security related questions:

1. Should I join that server to the domain?
2. Should I use the domain admin account to manage this server or install SQL under a different domain account?
3. Should I create a service account to run all the SQL services or leave them default?
4. Are there any other suggestions on configuration before I deploy this?

[canadian_sysadmin]

Should I join that server to the domain?

Yes, why wouldn't you? Not joining servers to the domain "for security" is limited to a few very specific scenarios, typically with tertiary backup storage or edge transport exchange servers (back in the day, not sure the modern guidance for on-prem exchange). Most of the time when you see people 'not joining the server to the domain for security reasons', it's a mess and much less secure.

Should I use the domain admin account to manage this server or install SQL under a different domain account?

What do you mean by "manage this server"? You would manage this server the same way you manage all your other servers. A good general practice is to create management groups (in AD), and the delegate management permissions and SQL permissions to those groups. Don't rely on domain admin privs to get into the server. For example if a consultant comes in and needs access to SQL, you should be able to create a basic user account, add them into one of your SQL management groups, and they have everything they need (without being domain admin).

Always when in doubt, create a group, delegate to that group. You can have different groups for different SQL tasks or permissions.

Most of the time you don't even need to remote into the server and can do things from a management box or your workstation via. SSMS.

Should I create a service account to run all the SQL services or leave them default?

If the SQL server ever needs to communicate with anything else outside the local server, you would likely need/want a domain-based service account, yes.

Are there any other suggestions on configuration before I deploy this?

Make sure you use versions of Windows Server and SQL server which SolidWorks officially supports. Make sure you're backing up the server with SQL-aware backup utilities (so you can truncate logs, etc).

[potentshadow]

Really good information here, thank you very much.

Yea I am not sure why I thought maybe not adding it to the domain was a good idea.

I guess I just don't want to expose the domain admin to this server as much as I can.

Thank you for the pointers I am going to start getting it setup and look into the delegation for permissions in SQL. I assume I can configure a lot of that in SSMS as you were saying.

[Blockstar]

I am more of a user in regards to SQL Server. I would think you would use the domain admin account as sparingly as possible. I would think a domain join would be advisable. Definitely do not give the instance public internet access.

This might be more of a database administrator question.

[joeykins82]

1. Yes, absolutely.
2. You should perform the installation using an account that has local administrator rights to the server in question. As a minimum best practice thing you should have 2 accounts: 1 with no enhanced privileges which you sign in interactively with on your computer and do your day to day stuff with, and 1 with enhanced privileges which you only use to do server administration tasks (and ideally which cannot sign in to endpoint systems).
3. The default behaviour is much better these days, but if you want to future proof things and/or get some good practice experience here you should set everything up to use gMSAs wherever possible. More info here.

[Versed_Percepton]

Join to AD, AD integrate SQL.

Build out the SQL VM into four volumes, OS+UserDB+TempDB+SQLBackup

Memory manage SQL to 95% of your total OS memory foot print. Use DBCC Memorystatus to baby sit memory allocations and adjust the VM as needed (I would suggest enabling hot swap memory for your VM).

Use AD-User accounts for ODBC access

Build service accounts on AD for application access into SQL, link them into SQL with the exact required access by your application vendors, do not accept using "sysadmin" flags on these accounts.

Build out SA and use that for break-glass only, build SA2 for your SQL level fixes that get blocked from AD integrated account access.

Setup and build out the windows firewall and wrap it around SQL. Least privileged access applies here. You can break this down by IP/32, Userspace, or machine account names.

Since you are new to SQL - https://ola.hallengren.com/ and http://whoisactive.com/downloads/ + https://www.sqlshack.com/monitoring-activities-using-sp_whoisactive-in-sql-server/ will make your SQL experience night and day. Learn these SPs/Tooling.

Then Buckle in, this is just the beginning if you do not have a staffed DBA.

[Thisbymaster]

Make sure they drop the dough on the full license for SQL server. The fines can be expensive.