10

Quick...

How to use Windows Authentication to SQL Server with the Mono SQL Client running on Windows without a username+ password in the connection string?

More...

  • We have to use Mono to support multiple platforms for some components of our app
    This is an external limitation that we can't change

  • We will run the components that access the database only on Windows
    The portability/OS-agnostic features of the Mono SQL Client add no value

That is, any component running on non-Windows will not access the SQL Server database

  • The process runs under some Windows user (real user, service account, whatever)

  • Embedding username and passwords is a bad thing
    No matter what angle you come from

So, how can we enable the Mono SQL Client to read the NT Logon Token of the user running the process and pass this to SQL Server? Just like MS .net does?

  • Is there a flag or setting that isn't well documented

  • Do we need to implement our own extension?
    If so, are we really the first folk to want to do this?

There are 5 other questions (currently) tagged Mono and SQL-Server: they don't answer this...

MartW
  • 12,348
  • 3
  • 44
  • 68
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 3
    I don't have an answer for you, but one has to wonder if you could treat AD like the Kerberos server that it is and get a ticket to use for your authentication. This is a really interesting problem! – Ben Thul May 02 '12 at 17:05
  • @BenThul: We'll investigate it. Make it an answer please: I'll upvote. It may help us to solve the problem too. – gbn May 02 '12 at 19:49
  • Oh... I don't know that I'd consider what I have as an answer yet. I just hope it leads to one. – Ben Thul May 02 '12 at 20:39
  • As far as I know, SQL Server's Windows authentication relies on AD, which has no counterpart on UNIX or Linux. In that case, you should not expect Mono to have that implementation, unless you write it on your own. Mono usually focus the portable parts, due to its limited resources. – Lex Li May 07 '12 at 13:59
  • @Siva: yes, correct ( I mentioned it), but has anyone ever worked around this or published some extension? This is the point of my question... – gbn May 07 '12 at 14:10

2 Answers2

10

This is not as easy to accomplish as it sounds. As I'm sure you know, Mono SqlClient has support for NT authentication:

Has a connection string format for NT Authentication: Server=hostname;Database=databaseName;User ID=windowsDomain\windowsUserid;Password=windowsPassword;Integrated Security=SSPI

But of course, you want the simpler form of Integrated Security=SSPI and let the NT authentication handshake use the current process credentials. And here lies the problem. While trivial to retrieve the current process user name (identity), is impossible for a process to discover it's own credentials password. When doing NT authentication an Windows process does not actually do the authentication, but instead is asking the Locas Security Authority (aka. LSASS.EXE, trivia: don't attach a debugger to it ;) ) to authenticate this process. Which means that any library that wants to achieve the same must use the same protocol, ie. ask LSA to authenticate it. The actual details, for the curious, are in the sequence of AcquireCredentialHandle, InitializeSecurityContext, AcceptSecurityContext as described in Using SSPI. I did not study the mono source for SqlClient, but I'm pretty sure they use some GSS-API library for the authentication, not SSPI. therefore, by definition, they require to know the password since they are going to do the Kerberos exchange themselves, not ask LSA to do it on their behalf.

This is, as you can tell, speculation and more of a guess on my side, but I would be surprised to hear a different story. While it is certainly possible to fork or patch Mono.Data.Tds and modify the authentication implementation to use SSPI instead of GSS, this would, by definition, be a non-portable Windows specific implementation. I would guess there is little incentive for it given that the #1 attraction point of Mono is that is not Windows specific. I'm afraid you are going to have to implement it on your own.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • We have to use Mono for different reasons: but non-portability isn't an issue for the components that connect to the database. And we can avoid passwords in plaintext if we do implement it – gbn May 07 '12 at 19:26
  • I'm not saying it doesn't make sense for *you*. I'm saying is probably not implemented by mono. – Remus Rusanu May 07 '12 at 19:55
  • 3
    @RemusRusanu explanation is correct. Mono does not implement **integrated** password-less connections to SQL server for the reasons he stated. Of course this is all open source so it's likely possible to add this to non-Windows platform. Be sure to share your findings with the community :-) – poupou May 08 '12 at 13:53
1

Use NTLM Authorization Proxy Server and connect to SQL Server through the proxy.

Bahribayli
  • 346
  • 2
  • 11