VPNs or peer-to-peer & Trusted Windows authentication

[tomcon...@gmail.com]

Hi all,

(networking is not my strong point!)

I use trusted windows authentication and things work fine standalone, normal networks and terminal servers.

In the past had similar issues with peer-to-peer networks, somehow got things working but never quite nailed the rules and now have to get the app working over VPN and am coming up against similar issues.  Crucially, the app needs to issue a role, e.g. APPUSERS to all active users, so normally it is:
GRANT APPUSERS TO "domain\windowslogin"
and windowslogin user is matched on the application users table

In a peer-to-peer or certain type of VPNs the client can be a local admin so connects as sysdba, not ideal, but not really the sticking point: 
It is that I can't find the correct syntax for the  domain part of the "domain\windowslogin" element (see above) in these environments. The machine may just be part of the workgroup. Anybody been here or know if it is possible?

Thanks

Tom

[Dimitry Sibiryakov]

08.02.2021 22:00, tomcon...@gmail.com wrote:
> It is that I can't find the correct syntax for the  domain part of the
> "domain\windowslogin" element (see above) in these environments. The machine may just be
> part of the workgroup. Anybody been here or know if it is possible?

For local Windows accounts the domain part of the name is naturally a local computer's
name as shown by USERDOMAIN environment variable and user management console.

--
WBR, SD.

[tomcon...@gmail.com]

Thanks, I'll look into this further but it doesn't seem to work in some situations (at least), 

as we have tried renaming the computer earlier today with no joy.

All the best,

Tom

[tomcon...@gmail.com]

Update: 

Unfortunately it doesn't work this morning in tests on the actual VPN

(i.e. granting role and logging in without any prompt using USERDOMAIN\windowsaccount)

In this scenario (this only one where it does do this) Firebird/AnyDac complains it needs a firebird username and login
Don't know what I can do that's different ...

Tom

[Mark Rotteveel]

On 09-02-2021 10:02, tomcon...@gmail.com wrote:
> Update:
> Unfortunately it doesn't work this morning in tests on the actual VPN
> (i.e. granting role and logging in without any prompt using
> USERDOMAIN\windowsaccount)
> In this scenario (this only one where it does do this) Firebird/AnyDac
> complains it needs a firebird username and login
> Don't know what I can do that's different ...

Are you literally using USERDOMAIN? Because that is not going to work.
The actual domain name is the value of the USERDOMAIN environment variable.

E.g. to get the value, on the command prompt on the user's host, execute
echo %USERDOMAIN%

Mark
--
Mark Rotteveel

[Tom Conlon]

No, but thanks for trying Mark.

Am using value of USERDOMAIN as reported by the SET command.

The machine name/USERDOMAIN value does have a hyphen in it, but we renamed another machine in a test yesterday to lose the hyphen and that didn't seem to make any difference at all.

KR

Tom

--
You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/aS2vXQyNgrA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/490aced1-8d66-99d2-de5d-77bb9bdca4c1%40lawinegevaar.nl.

[Dimitry Sibiryakov]

09.02.2021 10:02, tomcon...@gmail.com wrote:
> Unfortunately it doesn't work this morning in tests on the actual VPN
> (i.e. granting role and logging in without any prompt using USERDOMAIN\windowsaccount)

Naturally it cannot work with remote connection because there is no authority the
server can ask to confirm your credentials.
Remote trusted authentication is impossible without Windows Domain.

--
WBR, SD.

[Tom Conlon]

Ok, thanks for the clarification, it's not something that is immediately obvious from the documentation so perhaps these two installation scenarios (VPN & peer-to-peer) could be mentioned as not supporting trusted Windows authentication if possible.

Thanks

Tom

--
You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/aS2vXQyNgrA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/9a712aa2-2cdd-6d38-1b94-766c50e31d1d%40ibphoenix.com.

[Dimitry Sibiryakov]

09.02.2021 12:36, Tom Conlon wrote:
> Ok, thanks for the clarification, it's not something that is immediately obvious from the
> documentation so perhaps these two installation scenarios (VPN & peer-to-peer) could be
> mentioned as not supporting trusted Windows authentication if possible.

VPN doesn't prevent using of Windows Domain.
From the name "_trusted_ authentication" should be obvious that server must have
someone trustworthy to confirm you identity. In peer network there is no such principal.

https://docs.microsoft.com/en-us/windows-server/security/windows-authentication/windows-authentication-concepts

--
WBR, SD.

[Tom Conlon]

> From the name "_trusted_ authentication" should be obvious that server must have

> someone trustworthy to confirm you identity. In peer network there is no such principal.

Ok but as mentioned from the outset of my first post, I'm not a networking but a software/database guy.

It may be obvious to some but certainly not all I'd say with respect.  

Looks like the VPN situation can either be on a domain or not. If not, then this is the same case as peer-to-peer.

Also, I was expecting that even if not on a domain, the client machine user would be presented to the "server" as machinename/windowsusername and that the another aspect of the "trusted" part was that the user had been trusted/able to log into the local windows account.

Therefore not so obvious in some ways Dmitry plus with the 2 possibilities with VPNs & a no-go on peer-to-peer personally I would have thought a note about this in the trusted windows authentication documentation would be helpful, esp to non-networking specialists.

KR

Tom

--
You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/aS2vXQyNgrA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/66b8cc24-e4f0-0afc-bd92-7b00406d10d9%40ibphoenix.com.

[Mark Rotteveel]

On 09-02-2021 13:05, Tom Conlon wrote:
> Also, I was expecting that even if not on a domain, the client machine
> user would be presented to the "server" as
> machinename/windowsusername and that the another aspect of the "trusted"
> part was that the user had been trusted/able to log into the local
> windows account.

That is not how it works, because that would be very insecure, as any
John Doe could hookup a machine to the network, login as Administrator
or impersonate any user and then login to Firebird as that user.

Very simplified: Windows SSPI as used by Firebird is a protocol where
the client machine presents a token as credentials, and the server asks
its Windows host to verify that credential. The only way that the token
can be verified is if both machines are in the same domain (though maybe
federated domains could work as well, not sure), or the client and
server are the same machine.

As far as I understand it, the "trusted" in trusted authentication
doesn't mean that Firebird simply believes the client, it refers to the
fact that Firebird itself doesn't perform the authentication, but
delegates that responsibility to a third-party (it trusts the Windows host).

NOTE: I might have some details wrong, because I'm not an expert in this
area.

> Therefore not so obvious in some ways Dmitry plus with the 2
> possibilities with VPNs & a no-go on peer-to-peer personally I would
> have thought a note about this in the trusted windows authentication
> documentation would be helpful, esp to non-networking specialists.

Yes, the documentation is not very clear in this area. To be honest,
until recently I had similar expectations as you have.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

09.02.2021 13:05, Tom Conlon wrote:
> I would have thought a note about this in the trusted windows authentication documentation
> would be helpful, esp to non-networking specialists.

It is generally bad idea to add into Firebird documentation pieces that are not related
to Firebird but are part of common knowledge or basic principles of used platforms. That
including "how to turn computer on", "how to edit firebird.conf" or "how auth method XXX
is working".
Google is helpful in most of these cases.

--
WBR, SD.

[Mark Rotteveel]

I disagree, because without more specific information it is pretty hard
to find the right information (eg Windows SSPI has more extensive
authentication options than Firebird actually uses, nor is it easy to
find an explanation in laymans terms).

Mark
--
Mark Rotteveel

[Tom Conlon]

>  It is generally bad idea to add into Firebird documentation pieces
> that are not related to Firebird but are part of common knowledge or
> basic principles of used platforms.

Agreed that generally it's not good to add basic knowledge or unnecessary information to documentation.

This is not one of those situations though and I certainly wouldn't characterize this as "common knowledge or basic principles of used platforms". For networking specialists or people with aptitude for this area then they might be expected to know this but not others and Firebird is first and foremost a database product after all.

There's quite a lot of variety in connection terms:  

Standalone, embedded, peer-to-peer, VPN in and out of domain, network user, Terminal server user

The trusted Windows authentication method has a few gotchas depending on connection method and I've spent a lot of time on this both recently and in the past. Am sure I'm not the only one.

KR

Tom