SqlServerDsc: Support Sql Server on Linux

[nabil-becker]

I havent found any solid documentation, so I'm not sure if there is any support for this.

Is there anyway to use DSC to manage a Sql Server on Linux instance?

I saw that there was an open ticket to add SQL Auth for the connection, but that looks like it was last updated 2017

[nabil-becker]

I updated my local repo to allow me to specify a set of SqlCredentials for the SqlLogin module, and it works against a Linux instance

[johlju]

I have never tested this, but it would be awesome to have it work. I have just assumed that it did already work. But if it is not possible to use PsDscRunAsCredentials in Linux then I can see it is a problem. On Windows the built-in parameter PsDscRunAsCredentials runs the resource in that user context . If that isn't available on Linux then all resource probably need to be updated to support passing credentials by adding a new credential parameter, like you did. 🤔

Could you share the changes you did to the resource code? Did you need to do anything more?

[nabil-becker]

Yeah I can show the changes I made to the SqlLogin resource. Since it looks like its the SMO libraries I would assume it will work with Linux as well, we mainly just need a way to authenticate with SQL since we cant assume Linux SQL Servers will support windows authentication. Ill update this with the changes I made when I have some time.

[gaelcolas]

A very quick note here: PsDscRunAsCredentials is not working on Linux, correct, as we don't have an LCM. It's running on the current user session, so by changing the context of Invoke-DscResource you can get it to "run as". For instance, using invoke-command or start-job with the credential parameter. I think longer run we should have a proxy command Invoke-DscResourceAs that just extracts PsDscRunAsCredentials from the property hashtable.

[johlju]

@huggabear91 Yes, I understand you issue now. In this case the SQL Server instance does not know of any other accounts than the SQL Authentication accounts in the SQL Server instance. If the SQL Server instance is only using SQL authentication then none of the resources support that now (I think). The DSC resources assume that the account that run the resources have permission in the SQL Server instance. Prior to PsDscRunAsCredential (in PS 4.0) we needed to add a parameter Credential, if there are a resource left with that parameter then that resource will probably work, but I think we removed all of those a while back. But the parameter name you suggested (SqlCredentials) is better since it will distinguish it from the old Credential and better show the parameter does.

[johlju]

But it would fairly easy to add the support for it in the resource since the helper function Connect-SQL can take the SqlLogin type plus credentials. 🤔 That helper function is used in most resources (probably all that connects to a SQL Server Database Engine instance). @huggabear91 was that the way you solved it? 🤔

[johlju]

Both the resource SqlScript and SqlScriptQuery seems to have a Credential parameter to authenticate using SQL Authentication. https://github.com/dsccommunity/SqlServerDsc/blob/41fab4a103b0a66525732c68464cb29590e8a46e/source/DSCResources/DSC_SqlScript/DSC_SqlScript.schema.mof#L9

Since we probably need to impersonate both as a SQL Authentication account and a Windows Authentication account on SQL Server Database Engine running on Linux we might need another parameter LoginType in addition to the SqlCredentials so it is clear what type of impersonation should be made. 🤔

[nabil-becker]

Here's the DSC_SqlLogin.psm1 I used to do it. Essentially I just added a parameter to specify a set of sql credentials

DSC_SqlLogin.psm1

I would image having them as a separate parameter would be best, because I suppose we also wouldn't want to assume that the system running the PowerShell script is the same system that is hosting the server (for us, we'd probably have an "agent" windows box that would run the PowerShell script to update a remote Linux box)

But that's my two cents on the matter.

[johlju]

@huggabear91 thanks for that information!

[johlju]

I suggest that we add these two schema properties (AuthenticationType and ImpersonateAsCredentials) to each resource module with the appropriate code. These should also replace the Credential parameter in both SqlScript and SqlScriptQuery (which will be a breaking change for those two resources).

    [Write, Description("Specifies which type of logon credential should be used. If set to `'WindowsAuthentication'` then impersonation is done using _Windows Authentication_ with the credentials specified in the parameter **ImpersonateAsCredential**. If set to `'SqlAuthentication'` then impersonation is done using _SQL Server_ Authentication with the credentials specified in the parameter **ImpersonateAsCredential**. If this parameter is set then the parameter **ImpersonateAsCredential** must also be set."), ValueMap{"WindowsAuthentication","SqlAuthentication"}, Values{"WindowsAuthentication","SqlAuthentication"}] String AuthenticationType;
    [Write, EmbeddedInstance("MSFT_Credential"), Description("Specifies the credentials that will be used to authenticate using impersonation when connecting to the SQL Server Database Engine instance. This parameter should not be set if using the built-in parameter **PsDscRunAsCredential**. If this parameter is set then the parameter **AuthenticationType** must also be set.")] String ImpersonateAsCredential;

Maybe we should create an issue for each resource and add that to a project board to track progress since it could be a big PR for someone to changed all resources at once? 🤔

If anyone want to contribute this I would happily review PRs.

[johlju]

If possible we should also add integration tests for SQL Server on Linux so we can verify the changes do work. At least SQL Authentication as Windows Authentication will require to much infrastructure for our current build pipeline.