Login Permission

Question

Login Permission

VastSQL

SSC-Dedicated

Points: 36144
More actions
March 1, 2016 at 4:35 am

#308952

Hi Experts,
We have a SQL Server and application uses login 'demo' to connect to DB which is having db_owner privilege. Developers can read the config file and they use this login to connect to SQL from there local installed SSMS, how can I prevent users from connecting using local SSMS?
TIA

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jon-183221 SSCommitted Points: 1908 More actions · Answer 1

I'm not sure that you could do this. If the SQL login details are in the configuration file and this is freely available then anybody who gets hold of it can use it to access the sql server.

Can you encrypt the details in the configuration file or reduce the privileges of the account you are using to readonly?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

VastSQL (3/1/2016)
Hi Experts,
We have a SQL Server and application uses login 'demo' to connect to DB which is having db_owner privilege. Developers can read the config file and they use this login to connect to SQL from there local installed SSMS, how can I prevent users from connecting using local SSMS?
TIA

You could use a login trigger to rollback connections coming from SSMS for these users

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jon-183221 SSCommitted Points: 1908 More actions · Answer 3

Could they not then just go and connect via Excel/Access or anything else?

VastSQL SSC-Dedicated Points: 36144 More actions · Answer 4

Thanks Perry & Jon,

Why cant use same idea suggested by perry in the opposite way? Instead of blocking SSMS will only allow appname=.net

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 5

VastSQL (3/2/2016)
Thanks Perry & Jon,
Why cant use same idea suggested by perry in the opposite way? Instead of blocking SSMS will only allow appname=.net

Just keep in mind that the application name can be spoofed quite easily, so depending on how stringent your requirements are that may not be enough.

Cheers!

VastSQL SSC-Dedicated Points: 36144 More actions · Answer 6

Thanks Jacob,

Is there anyway we can filter with hostname as well?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Jon-183221 (3/1/2016)
Could they not then just go and connect via Excel/Access or anything else?

Yes and you can also roll those back too 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉