Authentication to load data from on prem sql server to Lakehouse using copy activity

[SangeethaMenonk]

Is windows authentication supported for copying data using fabric copy activity from an on prem sql server via on prem data gateway? The Microsoft document mentions basic authentication as the supported type for data pipelines to connect to sql server, although it's not clear whether basic authentication would work with an on-prem gateway.

[v-kongfanf-msft · Accepted solution]

Thanks for the reply from @NandanHegde , please allow me to provide another insight:

Hi @NandanHegde ,

First confirm the connection method supported by the currently connected sql server instance and make sure that the local data gateway is on.

Then switch the connection information in the configuration copy activity to the windows authentication method, once the credential information provided is correct, then you can successfully connect to the local sql server data source.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NandanHegde]

You can use windows auth to access On Prem SQL servers :

basic auth is SQL auth