topic Re: Windows Authentication to SQL server via OPA in Workato Pros Discussion Board

Windows Authentication to SQL server via OPA

johny-prentice — Mon, 23 Jan 2023 05:00:39 GMT

Has anyone got this working... ?

Can you please assist me with how you set this up.

Theres no documentation and support have struggled to provide a clear answer.

Re: Windows Authentication to SQL server via OPA

meghan-legaspi — Mon, 23 Jan 2023 14:43:41 GMT

Hi @johny-prentice,

I recommend reviewing this article: https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-oauth2-client-creds-grant-flow#get-a-token

That resource was helpful to a few other members in the past.

@mppowe @susrith-kalluri @andrew-leung - any chance you have more advice?

Best,
Meghan

Re: Windows Authentication to SQL server via OPA

mppowe — Mon, 23 Jan 2023 14:51:42 GMT

I don't have much to offer b/c I wasn't involved in the setup here. If it helps any, the relevant block from our yml file has (with some changes of course), in the event that there's anything there that's helpful to you:

database:

SqlDev:

adapter: sqlserver

host: servername.american.edu

port: 1433

database: development

username: myWorkatoServiceAccountRunningTheWindowsService

password: {encrypted: 'blahblah'}

timeout: 30

Re: Windows Authentication to SQL server via OPA

johny-prentice — Mon, 23 Jan 2023 21:42:50 GMT

That format looks exactly the same format as using "SQL Authentication".

Re: Windows Authentication to SQL server via OPA

johny-prentice — Mon, 23 Jan 2023 21:44:01 GMT

I dont think thats relevant to this topic.

Re: Windows Authentication to SQL server via OPA

mroldanvega — Thu, 26 Jan 2023 00:47:42 GMT

You need to also add the 'authentication' property, either Active Directory Password or in your case to use Windows Authentication you need to enter Integrated Authentication. Please also remember you need to set the LogOn credentials on the Windows Service (OPA) so that it can successfully work.

Re: Windows Authentication to SQL server via OPA

johny-prentice — Mon, 06 Feb 2023 20:16:58 GMT

My main issue was the "OPA" was setup as cloud.
So it ignores any profile you setup in the config file on the OPA server.

I think this is new feature, that a lot of people aren't aware of.

Re: Windows Authentication to SQL server via OPA

mschoen — Thu, 03 Aug 2023 16:16:32 GMT

So that means in the Set-up connection i can't use "Directly in Workato", when I need to work with a Windows authentication, right?