4

How do I connect MS SQL Server using Windows Authentication using Airflow Web UI or modifying existing SQLAlchemy/pymssql Python modules?

I have SQL Server SSIS packages which can't use this option https://joethebusinessintelligenceguy.wordpress.com/2013/08/14/ssis-2012-using-sql-authentication-with-dont-save-sensitive-successfully/) hence I'm trying to start the SSIS steps by using Windows Authentication.

I have found following links showing that support indeed exists but I don't know how to implement the same on Airflow -installation (which file to modify or where to create new conn_id).

Community
  • 1
  • 1
Mika Heino
  • 221
  • 1
  • 3
  • 4
  • 1
    Those articles are assuming that you are running the python code on windows, where the "trusted authentication" part of the connection string is going to pick up the windows login of the user running the script, or if you're running it via a service then that service account name will be used as the windows authentication user. In the case of Airflow, you're running on Linux, so its not straight forward. You need Kerberos and a fair bit of setup http://www.easysoft.com/products/data_access/odbc-sql-server-driver/kerberos.html#accessing-sql-server-kerberos-service-linux – Davos May 22 '17 at 12:23
  • Update on this: I didn't resolve the Windows -authentication issue nor did I either try to set up Kerberos -authentication on my Linux box. Instead I resolved the issue by separating my SSIS packages into new SQL Server jobs and starting them by implementing following code: `s1 = MsSqlOperator( task_id='staging___st_crm_control', sql='EXEC msdb.dbo.sp_start_job ''airflow_crm_ssis'' ;', mssql_conn_id='db2', dag=dag)` – Mika Heino Aug 11 '17 at 12:57
  • Glad you sorted it out. Sounds like that mssql_conn_id points to a configuration for a SQL Authentication, not a Windows Authentication, is that what you're doing? I would have suggested that but your question sounded like you didn't have that option. – Davos Aug 12 '17 at 12:49
  • Yes, mssql_conn_id is pointing to SQL Authentication. Using SQL Authentication is not the ideal solution as I don't know how long my SSIS packages will run. Airflow will only report how long it took to start the job. So in a way you're were correct when you say that I didn't had that option. – Mika Heino Aug 13 '17 at 14:05

1 Answers1

2

To use Windows Authentication, the Apache Airflow will need same windows access that your Sql Server, and that's strongly not recommended.

So probably is better use an User and Password to Connect your SqlDatabase.

Apache Airflow does it have an info in your documentation to make connections in SQL SERVER in this link

Leonardo Gasparini
  • 79
  • 7