January 20, 2021 at 12:50 am
Currently, while installing SQL server we use a local linux account to run the SQL service. But is there a way to change the user to be domain account just like in windows for security purposes? I didnt see any documentation related to service account other than using "mssql" as local user to run the service.
Please let me know if you have any ideas or solutions for this problem.
January 20, 2021 at 7:56 pm
My opinion (I do not have SQL Server on Linux installed), I see no reason why you wouldn't be able to do that.
My expectation as to why it isn't well documented is that Linux, out of the box, doesn't do AD authentication. So you'd need to install something to handle AD authentication on the server first, and after that you could adjust the service to start using that account. You would also need to ensure permissions on the account were set up correctly.
So I think the first step would be to get AD authentication working on the server (PAM I think can handle this, but it has been AGES since I tried setting that up), and once that is working, it would just be a matter of changing who the service runs as.
If you are comfortable in Linux to install and setup PAM to get AD authentication working, changing the SQL Server service should be pretty easy. If you are not that comfortable in Linux, I would leave things alone while they are working. Troubleshooting problems in Linux is not the same as in Windows and you would have more hurdles to jump as you went and a LOT more logs to read through.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 21, 2021 at 5:43 am
Hi
I hope this information may help you-
Before you design AD Authentication, you need to:
1.Set up an AD Domain Controller (Windows) on your organization
2.Introduce SQL Server
Join SQL Server host to AD area:
Join your SQL Server Linux have with an Active Directory space regulator. For data on the most proficient method to join a functioning catalog area, see Join SQL Server on a Linux host to an Active Directory s
Make AD client (or MSA) for SQL Server and set SPN
1.On your area regulator, run the New-ADUser PowerShell order to make another AD client with a secret phrase that won't ever lapse. The accompanying model names the record mssql, however, the record name can be anything you like. You'll be incited to enter another secret phrase for the record.
PowerShellCopy
Import-Module ActiveDirectory
New-ADUser mssql -AccountPassword (Read-Host -AsSecureString "Enter Password") -PasswordNeverExpires $true -Enabled $true
2.Set the ServicePrincipalName (SPN) for this record utilizing the setspn.exe apparatus. The SPN should be organized precisely as indicated in the accompanying model. You can discover the completely qualified space name of the SQL Server have to machine by running hostname - all-fqdns on the SQL Server have. The TCP port ought to be 1433 except if you have arranged SQL Server to utilize an alternate port number.
2.Set the ServicePrincipalName (SPN) for this record utilizing the setspn.exe apparatus. The SPN should be organized precisely as indicated in the accompanying model. You can discover the completely qualified space name of the SQL Server have to machine by running hostname - all-fqdns on the SQL Server have. The TCP port ought to be 1433 except if you have arranged SQL Server to utilize an alternate port number.
PowerShellCopy
setspn -A MSSQLSvc/<fully qualified domain name of host machine>:<tcp port> mssql
setspn -A MSSQLSvc/<netbios name of the host machine>:<tcp port> mssql
Make AD-based logins in Transact-SQL
1.Connect to SQL Server and create a new, AD-based login:
SQLCopy
CREATE LOGIN [CONTOSO\user] FROM WINDOWS;
2.Check that the login is currently recorded in the sys.server_principals framework list see:
2.Check that the login is currently recorded in the sys.server_principals framework list see:
SQLCopy
SELECT name FROM sys.server_principals;
Interface with SQL Server utilizing AD Authentication:
Sign in to a customer machine utilizing your space qualifications. Presently you can associate with SQL Server without returning your secret phrase by utilizing AD Authentication. On the off chance that you make a login for an AD gathering, any AD client who is an individual from that gathering can associate similarly.
The particular association string boundary for customers to utilize AD Authentication relies upon which driver you are utilizing.
Regards,
January 22, 2021 at 5:29 am
Thanks for the replies.
I was able to do the AD authentication and add AD domain accounts to SQL logins. But I am not able to change the SQL server service account to run as Domain user. It is still using local linux account. This is where I am stuck. Any more thoughts?
February 3, 2021 at 10:07 am
Hii..
I hope this may help you-
The service startup account defines the Microsoft Windows account in which SQL Server Agent runs and its network permissions. SQL Server Agent runs as a specified user account. You select an account for the SQL Server Agent service by using SQL Server Configuration Manager, where you can choose from the following options:
Built-in account. You can choose from a list of the following built-in Windows service accounts:
Windows Domain Account Permissions
For improved security, select This account, which specifies a Windows domain account. The Windows domain account that you specify must have the following permissions:
In all Windows versions, permission to log on as a service (SeServiceLogonRight)
In Windows servers, the account that the SQL Server Agent Service runs as requires the following permissions to be able to support SQL Server Agent proxies.
Permission to bypass traverse checking (SeChangeNotifyPrivilege)
Permission to replace a process-level token (SeAssignPrimaryTokenPrivilege)
Permission to adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
Permission to access this computer from the network (SeNetworkLogonRight)
SQL Server Role Membership
The account that the SQL Server Agent service runs must be a member of the following SQL Server roles:
The account must be a member of the sysadmin fixed server role.
To use multiserver job processing, the account must be a member of the msdb database role TargetServersRole on the master server.
Regards,
Srija
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply