[solved] host-based authentication possible?

Question

[solved] host-based authentication possible?

mh

Old Hand

Points: 360
More actions
September 15, 2022 at 8:59 am

#4087971
Hi!
is host-based authentication possible? e.g. :
- userA is allowed to login from 192.168.80.0/24
- userB is allowed to login only from 127.0.0.1
- userC is allowed to login from any host
- This topic was modified 1 year, 7 months ago by mh.
- This topic was modified 1 year, 7 months ago by mh.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Not natively. You could use something like server-level triggers that occur on a Logon event, but I'm not a massive fan of such things.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

mh Old Hand Points: 360 More actions · Answer 2

Thanks!

Thom A wrote:

but I'm not a massive fan of such things.

Can you please specify? Are there any pitfalls when I use it

Thom A SSC Guru Points: 99126 More actions · Answer 3

mh wrote:

Thanks!
Thom A wrote:
but I'm not a massive fan of such things.
Can you please specify? Are there any pitfalls when I use it

Pitfuls? A couple would be how to maintain it and the risk that involves.

You would either need to put all the logic in the trigger, which makes things difficult to maintain (as you have to make changes to the trigger every time your rules change), which is less than ideal and needs a high level of permissions.

Alternatively, you could use a database to store the data, however, then you need to ensure that that data is accessible for all LOGINs when accessing the server, but if the database or table isn't available when trying to authenticate to the server all login attempts would fail. The advantage, however, is that management of access would be easier.

There is a section of the documentation specifically on Logon Triggers.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Michael L John One Orange Chip Points: 27042 More actions · Answer 4

You could restrict this via firewall rules, and possibly some of the things built into Active Directory.

But really, this sound like a maintenance nightmare. What are you trying to accomplish?

This reply was modified 1 year, 7 months ago by Michael L John.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

mh Old Hand Points: 360 More actions · Answer 5

Thanks all,

to be honest I didn't knew logon triggers before. Now I learnt something new 🙂

But I agree that these things are too "buggy". Maybe easy to setup, but hard to maintain and risky to accidentally allow unwanted connections.

Basically I like to keep things simple and avoid (if possible) complex solutions in server/system administration.

Firewall rules are what I am using at the moment in our setup. I only wondered if there was some kind of host-based authentication in SQL-Server.

Now I know its not - so thanks again 🙂