Using Windows Authentication from a Linux Webserver?

[benyanke]

I'm working on an app which will run on a Linux webserver, but needs to connect to a MSSQL server for some of our data.

When I try to connect, it rejects me because the server is configured to accept Windows authentication, not native SQL authentication.

(For searchability, the error says: Login failed for user 'USERNAME'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.)

The error returned in PHP is:

Error information: 
SQLSTATE: 28000
Code: 18456
Message: [unixODBC][Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'byanke'.

Is there any way around this when running on a linux application server, or does the plugin force use of SQL authentication?

[benyanke]

Self-resolved: With further research it became clear that Windows Authentication is only possible on domain-connected systems. As I'm not interested in putting webservers on the domain or give them domain accounts, the only way to accomplish the goal (someone correct me if I'm wrong) is to use SQL auth.

[cmosguy]

@benyanke i am also trying to figure out how to do Windows Auth from linux webserver, did you ever find a solution to this?

[benyanke]

My research lead me to the point where it became clear the only way to really do this is getting the server to join the AD domain, which I'm not interested in doing.

In my case, I dropped it, and pestered my sysadmin to enable SQL auth alongside kerberos (usually branded as "windows auth").

[Catscrash]

is there any news in this? is this on the roadmap somehow? On Windows the Server doesn't need to be in the Domain for Windows Authentication - Why does it on Linux?

[yitam]

Hi @Catscrash,

For your interest, you might want to consider the alternative authentication technology, Azure Active Directory

PHP drivers provide partial support of Azure AD across the platforms, and please check our documentation, Connect Using Azure Active Directory Authentication. We plan to continue to add more support in the near future.

[Catscrash]

@benyanke you said it would work if the server had joined the domain - could you explain how? I'm trying to do that at the moment. The server joined the domain, I can run kinit... on the shell and it works great, but how to make PHP in apache use the kerberos ticket?

ERROR:database connection failure (Host: XXX.YYYlocal; Database: DBNAME); native message: asp_db_mssql_sqlsrv->connect(): failed. error: code: 851968, message: #1: SQLSTATE: HY000; code: 851968; message: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1002)

[benyanke]

Nope, never did get working - which was why I switched to sql auth.

[ph777]

@yitam, it's great you offer Azure Active Directory. However, this is not an option for our customer. Is there a way to use sqlsrv to connect from Linux machine to an SQL Server using Windows Authentication. If there is, is a howto available? It would by HIGHLY appreciated. Thanks.

[yitam]

@ph777 You may want to consider Using Integrated Authentication. Hope this answers your question.

[Catscrash]

@yitam this requires the server to be joined into the domain, which is most often not what we want

[yitam]

@Catscrash I'm confused... thought your comment above indicated that you had your server joined the domain? Besides, have you tested Kerberos using sqlcmd?

[ph777]

@yitam Thanks for your answer. Using Integrated Authentication is exactly what we need. Joining a machine into the domain is not a problem for us (the post above was not from me, but from @catscrash). However, there is no howto on how to configure Linux + Apache.

We have Kerberos installed, joined the domain, acquired a ticket. However, sqlcmd -E says

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSPI Provider: Server not found in Kerberos database.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Cannot generate SSPI context.

It's not clear what to do next. Can you help?

Further, if we get this working, how are we supposed to configure Apache, so that a PHP script can call sqlsrv_connect with integrated authentication? This information is what we miss the most.

Thank you for all your help.

[v-chojas]

You need to register an SPN for the server. See here for more information:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections

[Catscrash]

Any news on this? Before with FreeTDS it was always possible to use windows authentication without having the server to join the domain. I don't need password-less login, SSO or anything like this, I just want to be able to give "Domain\Username" and "password" as login, just like I can when using windows...

Our Servers are connected to customers databases via Site2Site VPN, it's not possible to have our servers join their Domain. But with freetds + php-mssql it was no issue to use accounts that have been created on the sql server as windows authentication, now it's not anymore. Would it not be possible to bring this back, please?

[v-chojas]

Try to run your application using the domain account with the runas command.

[Catscrash]

not possible, there's no trust between the domains. Also these are linux machines

[v-chojas]

You can use Kerberos - see link "Using Integrated Authentication" above for how to configure it. If you can obtain a Kerberos ticket from the domain controller with the right credentials, the driver will be able to use that to authenticate to SQL.

[fmaurice]

@Catscrash : Hi did you succeed to connect to the server ? I have the same problem : I can connect to a SQL server from the command line, with kinit, but not behind Apache...

[Alchantness]

@fmaurice Hey! Do you have an answer for this problem?

[v-chojas]

The user the process is running as needs to have the Kerberos ticket.