How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data ProviderUser...
Can you 'upgrade' leather armor to studded leather armor without purchasing the new armor directly?
Book where the good guy lives backwards through time and the bad guy lives forward
Can I become debt free or should I file for bankruptcy? How do I manage my debt and finances?
Is my plan for fixing my water heater leak bad?
How to count occurrences of Friday 13th
How do I construct an nxn matrix?
Do my Windows system binaries contain sensitive information?
What is a term for a function that when called repeatedly, has the same effect as calling once?
How to acknowledge an embarrassing job interview, now that I work directly with the interviewer?
How to avoid being sexist when trying to employ someone to function in a very sexist environment?
Can chords be played on the flute?
Do authors have to be politically correct in article-writing?
Why does the author believe that the central mass that gas cloud HCN-0.009-0.044 orbits is smaller than our solar system?
Unexpected behavior of Bash script: First executes function, afterwards executes alias
fontspec breaks capital greek letters in DeclareMathOperator
Which aircraft had such a luxurious-looking navigator's station?
POVM three-qubit circuit for symmetric quantum states
Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?
Is divide-by-zero a security vulnerability?
Skis versus snow shoes - when to choose which for travelling the backcountry?
How to properly claim credit for peer review?
Is there a frame of reference in which I was born before I was conceived?
Why proton concentration is divided by 10⁻⁷?
Hacker Rank: Array left rotation
How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider
User can alter procedures without permissionSQl Server 2008 R2 - sa can login but not access databaseMirrored local accounts to connect to database on different server and domainHow to identify a client application whose host_process_id points to sqlserver.exeUnable to login to SQL Server using SSMS and SQL agent failingConfused Logins and Users in SQL ServerNew SQL Server from backup; application has successful login on 1 database, but fails on another: 0x80040e4dCreate a user account login that cannot login from SQL Server Management StudioSQL Server login failed, but user has permissionsMysql Workbench crashing when connecting
We have a situation where Developers do not have any UPDATE
permissions, BUT they work with applications and see connection strings -> they know passwords from some SQL accounts (example SQLLogin1
) that have UPDATE permissions. Our operations currently are not perfect, and sometimes production data needs to be modified (no GUI for that yet).
Instead of contacting DBA, and asking him to modify the data, Developer would (improperly) use SQL account SQLLogin1
(that has permission to modify the data),
and connect over SQL Server Management Studio to modify the data himself.
DBA can not change password for SQLLogin1
without Developer seeing the new connection string and new password, since the application connection string that uses SQLLogin1
is maintained by Developer.
Question:
Is there a way to deny access to SQLLogin1
SQL login, but only if it is connecting over SSMS?
At the same time if SQLLogin1
is connecting over .Net SqlClient Data Provider
(program_name
in the sys.dm_exec_sessions
), it must be allowed to login.
This way we want to not let Developer connect over SSMS using SQLLogin1
, while the application that is using SQLLogin1
, would still be able to connect.
sql-server permissions access-control
|
show 3 more comments
We have a situation where Developers do not have any UPDATE
permissions, BUT they work with applications and see connection strings -> they know passwords from some SQL accounts (example SQLLogin1
) that have UPDATE permissions. Our operations currently are not perfect, and sometimes production data needs to be modified (no GUI for that yet).
Instead of contacting DBA, and asking him to modify the data, Developer would (improperly) use SQL account SQLLogin1
(that has permission to modify the data),
and connect over SQL Server Management Studio to modify the data himself.
DBA can not change password for SQLLogin1
without Developer seeing the new connection string and new password, since the application connection string that uses SQLLogin1
is maintained by Developer.
Question:
Is there a way to deny access to SQLLogin1
SQL login, but only if it is connecting over SSMS?
At the same time if SQLLogin1
is connecting over .Net SqlClient Data Provider
(program_name
in the sys.dm_exec_sessions
), it must be allowed to login.
This way we want to not let Developer connect over SSMS using SQLLogin1
, while the application that is using SQLLogin1
, would still be able to connect.
sql-server permissions access-control
2
You can use a server logon trigger to force a login failure when connecting to the server. Be careful when implementing one of these, if not coded properly you will be rejecting logins (make sure to test in proper test environment). docs.microsoft.com/en-us/sql/relational-databases/triggers/…
– EzLo
8 hours ago
3
While blocking SSMS might work, it's not a security solution. You can set theprogram_name
to anything you want, even in SSMS by settingApplication Name=whatever
in the Additional Connection Parameters. For a security solution, prevent the devs from seeing the passwords for production databases.
– David Browne - Microsoft
6 hours ago
3
How would this prevent them from making a console application to run the same update statement? If anything you're making this ugly process even more ugly.
– Deacon
6 hours ago
4
"BUT they work with applications and see connection strings -> they know passwords from some SQL accounts ... that have UPDATE permissions." There's your problem. Have proper account management and don't give them passwords they're not authorized to use. If you don't trust them to handle the account responsibly, then they shouldn't have it.
– jpmc26
5 hours ago
4
I think @jpmc26 is on the money. This sounds like a XY-problem where you shouldn’t worry about SSMS access but about developers having credentials they shouldn’t have. There are ways to handle the secrets so that devs don’t need production credentials - and it sounds like that’s what you are after.
– vhu
4 hours ago
|
show 3 more comments
We have a situation where Developers do not have any UPDATE
permissions, BUT they work with applications and see connection strings -> they know passwords from some SQL accounts (example SQLLogin1
) that have UPDATE permissions. Our operations currently are not perfect, and sometimes production data needs to be modified (no GUI for that yet).
Instead of contacting DBA, and asking him to modify the data, Developer would (improperly) use SQL account SQLLogin1
(that has permission to modify the data),
and connect over SQL Server Management Studio to modify the data himself.
DBA can not change password for SQLLogin1
without Developer seeing the new connection string and new password, since the application connection string that uses SQLLogin1
is maintained by Developer.
Question:
Is there a way to deny access to SQLLogin1
SQL login, but only if it is connecting over SSMS?
At the same time if SQLLogin1
is connecting over .Net SqlClient Data Provider
(program_name
in the sys.dm_exec_sessions
), it must be allowed to login.
This way we want to not let Developer connect over SSMS using SQLLogin1
, while the application that is using SQLLogin1
, would still be able to connect.
sql-server permissions access-control
We have a situation where Developers do not have any UPDATE
permissions, BUT they work with applications and see connection strings -> they know passwords from some SQL accounts (example SQLLogin1
) that have UPDATE permissions. Our operations currently are not perfect, and sometimes production data needs to be modified (no GUI for that yet).
Instead of contacting DBA, and asking him to modify the data, Developer would (improperly) use SQL account SQLLogin1
(that has permission to modify the data),
and connect over SQL Server Management Studio to modify the data himself.
DBA can not change password for SQLLogin1
without Developer seeing the new connection string and new password, since the application connection string that uses SQLLogin1
is maintained by Developer.
Question:
Is there a way to deny access to SQLLogin1
SQL login, but only if it is connecting over SSMS?
At the same time if SQLLogin1
is connecting over .Net SqlClient Data Provider
(program_name
in the sys.dm_exec_sessions
), it must be allowed to login.
This way we want to not let Developer connect over SSMS using SQLLogin1
, while the application that is using SQLLogin1
, would still be able to connect.
sql-server permissions access-control
sql-server permissions access-control
edited 7 hours ago
EzLo
2,2661420
2,2661420
asked 8 hours ago
voodoo_shvoodoo_sh
1967
1967
2
You can use a server logon trigger to force a login failure when connecting to the server. Be careful when implementing one of these, if not coded properly you will be rejecting logins (make sure to test in proper test environment). docs.microsoft.com/en-us/sql/relational-databases/triggers/…
– EzLo
8 hours ago
3
While blocking SSMS might work, it's not a security solution. You can set theprogram_name
to anything you want, even in SSMS by settingApplication Name=whatever
in the Additional Connection Parameters. For a security solution, prevent the devs from seeing the passwords for production databases.
– David Browne - Microsoft
6 hours ago
3
How would this prevent them from making a console application to run the same update statement? If anything you're making this ugly process even more ugly.
– Deacon
6 hours ago
4
"BUT they work with applications and see connection strings -> they know passwords from some SQL accounts ... that have UPDATE permissions." There's your problem. Have proper account management and don't give them passwords they're not authorized to use. If you don't trust them to handle the account responsibly, then they shouldn't have it.
– jpmc26
5 hours ago
4
I think @jpmc26 is on the money. This sounds like a XY-problem where you shouldn’t worry about SSMS access but about developers having credentials they shouldn’t have. There are ways to handle the secrets so that devs don’t need production credentials - and it sounds like that’s what you are after.
– vhu
4 hours ago
|
show 3 more comments
2
You can use a server logon trigger to force a login failure when connecting to the server. Be careful when implementing one of these, if not coded properly you will be rejecting logins (make sure to test in proper test environment). docs.microsoft.com/en-us/sql/relational-databases/triggers/…
– EzLo
8 hours ago
3
While blocking SSMS might work, it's not a security solution. You can set theprogram_name
to anything you want, even in SSMS by settingApplication Name=whatever
in the Additional Connection Parameters. For a security solution, prevent the devs from seeing the passwords for production databases.
– David Browne - Microsoft
6 hours ago
3
How would this prevent them from making a console application to run the same update statement? If anything you're making this ugly process even more ugly.
– Deacon
6 hours ago
4
"BUT they work with applications and see connection strings -> they know passwords from some SQL accounts ... that have UPDATE permissions." There's your problem. Have proper account management and don't give them passwords they're not authorized to use. If you don't trust them to handle the account responsibly, then they shouldn't have it.
– jpmc26
5 hours ago
4
I think @jpmc26 is on the money. This sounds like a XY-problem where you shouldn’t worry about SSMS access but about developers having credentials they shouldn’t have. There are ways to handle the secrets so that devs don’t need production credentials - and it sounds like that’s what you are after.
– vhu
4 hours ago
2
2
You can use a server logon trigger to force a login failure when connecting to the server. Be careful when implementing one of these, if not coded properly you will be rejecting logins (make sure to test in proper test environment). docs.microsoft.com/en-us/sql/relational-databases/triggers/…
– EzLo
8 hours ago
You can use a server logon trigger to force a login failure when connecting to the server. Be careful when implementing one of these, if not coded properly you will be rejecting logins (make sure to test in proper test environment). docs.microsoft.com/en-us/sql/relational-databases/triggers/…
– EzLo
8 hours ago
3
3
While blocking SSMS might work, it's not a security solution. You can set the
program_name
to anything you want, even in SSMS by setting Application Name=whatever
in the Additional Connection Parameters. For a security solution, prevent the devs from seeing the passwords for production databases.– David Browne - Microsoft
6 hours ago
While blocking SSMS might work, it's not a security solution. You can set the
program_name
to anything you want, even in SSMS by setting Application Name=whatever
in the Additional Connection Parameters. For a security solution, prevent the devs from seeing the passwords for production databases.– David Browne - Microsoft
6 hours ago
3
3
How would this prevent them from making a console application to run the same update statement? If anything you're making this ugly process even more ugly.
– Deacon
6 hours ago
How would this prevent them from making a console application to run the same update statement? If anything you're making this ugly process even more ugly.
– Deacon
6 hours ago
4
4
"BUT they work with applications and see connection strings -> they know passwords from some SQL accounts ... that have UPDATE permissions." There's your problem. Have proper account management and don't give them passwords they're not authorized to use. If you don't trust them to handle the account responsibly, then they shouldn't have it.
– jpmc26
5 hours ago
"BUT they work with applications and see connection strings -> they know passwords from some SQL accounts ... that have UPDATE permissions." There's your problem. Have proper account management and don't give them passwords they're not authorized to use. If you don't trust them to handle the account responsibly, then they shouldn't have it.
– jpmc26
5 hours ago
4
4
I think @jpmc26 is on the money. This sounds like a XY-problem where you shouldn’t worry about SSMS access but about developers having credentials they shouldn’t have. There are ways to handle the secrets so that devs don’t need production credentials - and it sounds like that’s what you are after.
– vhu
4 hours ago
I think @jpmc26 is on the money. This sounds like a XY-problem where you shouldn’t worry about SSMS access but about developers having credentials they shouldn’t have. There are ways to handle the secrets so that devs don’t need production credentials - and it sounds like that’s what you are after.
– vhu
4 hours ago
|
show 3 more comments
3 Answers
3
active
oldest
votes
You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using SSMS.
For example:
CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1
ON ALL SERVER FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() = N'SQLLogin1' AND PROGRAM_NAME() LIKE N'Microsoft SQL Server Management Studio%'
BEGIN
RAISERROR('Direct connection by SSMS refused.', 16, 1)
ROLLBACK
END
END
The ROLLBACK
inside the trigger will reject the connection (there's an implicit transaction wrapping the call to the trigger on the logon event).
Be careful when implementing logon triggers, if not coded properly you will be rejecting logins that should be able to login (including your own!). Make sure to test on test/dev environments first.
Keep in mind that this code is executed before the session is created, so system views that rely on the session id (SPID) won't contain the currently checked login until the triggers ends without rollback or high enough failure.
thank you! question - if I make any mistake in logon trigger, and it blocks even sysadmin account from logging in, is there still a way to get into SQL Server and disable logon trigger ?
– voodoo_sh
7 hours ago
2
You can drop the trigger without it firing if you connect with a DAC (dedicated administrator connection). It's a particular single-user connection you can issue against the server whenever things go wrong. It's usually used directly with sqlcmd, but you can do it with SSMS too. docs.microsoft.com/en-us/previous-versions/sql/…
– EzLo
7 hours ago
3
This will work for a few minutes, until the developer uses a different tool. You simply cannot keep a good developer out if they know a login with permissions.
– Joe
6 hours ago
This is more an policy solution than a security solution. IE the logon trigger makes it clear that it's against policy to directly connect to the production database. And since it's unlikely you can protect against a actually malicous developer anyway, that may be good enough.
– David Browne - Microsoft
6 hours ago
@voodoo_sh: Be prepared to find a backdoor in the program codebase.
– Joshua
4 hours ago
|
show 4 more comments
I think there is no reliable solution for your problem since Application Name
is modifiable parameter
that cam be changed by any user.
Here is how to change it within SSMS
:
In Connect to Database Object
dialog choose Options, open Additional Connection Parameters
and choose any name for Application Name
like this:
Now sys.dm_exec_sessions
DMV and Program_name() will show you what you passed in your connection string in Application Name
parameter:
add a comment |
You can't cut off a specific client, as already detailed in the other answers.
The solution is to remove the access privileges to the production systems from the developer's accounts.
Any change must be scripted and a dba will run the script.
Deployment is performed by a sysadmin; devs produce a package they give to someone with proper privileges and devs never see the configs used on production systems.
Debugging is arranged on a case by case basis with a copy of the production data in a staging environment as a preferred solution or a temporary account with limited privileges if needed.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231228%2fhow-to-deny-access-to-sql-server-to-certain-login-over-ssms-but-allow-over-net%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using SSMS.
For example:
CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1
ON ALL SERVER FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() = N'SQLLogin1' AND PROGRAM_NAME() LIKE N'Microsoft SQL Server Management Studio%'
BEGIN
RAISERROR('Direct connection by SSMS refused.', 16, 1)
ROLLBACK
END
END
The ROLLBACK
inside the trigger will reject the connection (there's an implicit transaction wrapping the call to the trigger on the logon event).
Be careful when implementing logon triggers, if not coded properly you will be rejecting logins that should be able to login (including your own!). Make sure to test on test/dev environments first.
Keep in mind that this code is executed before the session is created, so system views that rely on the session id (SPID) won't contain the currently checked login until the triggers ends without rollback or high enough failure.
thank you! question - if I make any mistake in logon trigger, and it blocks even sysadmin account from logging in, is there still a way to get into SQL Server and disable logon trigger ?
– voodoo_sh
7 hours ago
2
You can drop the trigger without it firing if you connect with a DAC (dedicated administrator connection). It's a particular single-user connection you can issue against the server whenever things go wrong. It's usually used directly with sqlcmd, but you can do it with SSMS too. docs.microsoft.com/en-us/previous-versions/sql/…
– EzLo
7 hours ago
3
This will work for a few minutes, until the developer uses a different tool. You simply cannot keep a good developer out if they know a login with permissions.
– Joe
6 hours ago
This is more an policy solution than a security solution. IE the logon trigger makes it clear that it's against policy to directly connect to the production database. And since it's unlikely you can protect against a actually malicous developer anyway, that may be good enough.
– David Browne - Microsoft
6 hours ago
@voodoo_sh: Be prepared to find a backdoor in the program codebase.
– Joshua
4 hours ago
|
show 4 more comments
You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using SSMS.
For example:
CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1
ON ALL SERVER FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() = N'SQLLogin1' AND PROGRAM_NAME() LIKE N'Microsoft SQL Server Management Studio%'
BEGIN
RAISERROR('Direct connection by SSMS refused.', 16, 1)
ROLLBACK
END
END
The ROLLBACK
inside the trigger will reject the connection (there's an implicit transaction wrapping the call to the trigger on the logon event).
Be careful when implementing logon triggers, if not coded properly you will be rejecting logins that should be able to login (including your own!). Make sure to test on test/dev environments first.
Keep in mind that this code is executed before the session is created, so system views that rely on the session id (SPID) won't contain the currently checked login until the triggers ends without rollback or high enough failure.
thank you! question - if I make any mistake in logon trigger, and it blocks even sysadmin account from logging in, is there still a way to get into SQL Server and disable logon trigger ?
– voodoo_sh
7 hours ago
2
You can drop the trigger without it firing if you connect with a DAC (dedicated administrator connection). It's a particular single-user connection you can issue against the server whenever things go wrong. It's usually used directly with sqlcmd, but you can do it with SSMS too. docs.microsoft.com/en-us/previous-versions/sql/…
– EzLo
7 hours ago
3
This will work for a few minutes, until the developer uses a different tool. You simply cannot keep a good developer out if they know a login with permissions.
– Joe
6 hours ago
This is more an policy solution than a security solution. IE the logon trigger makes it clear that it's against policy to directly connect to the production database. And since it's unlikely you can protect against a actually malicous developer anyway, that may be good enough.
– David Browne - Microsoft
6 hours ago
@voodoo_sh: Be prepared to find a backdoor in the program codebase.
– Joshua
4 hours ago
|
show 4 more comments
You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using SSMS.
For example:
CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1
ON ALL SERVER FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() = N'SQLLogin1' AND PROGRAM_NAME() LIKE N'Microsoft SQL Server Management Studio%'
BEGIN
RAISERROR('Direct connection by SSMS refused.', 16, 1)
ROLLBACK
END
END
The ROLLBACK
inside the trigger will reject the connection (there's an implicit transaction wrapping the call to the trigger on the logon event).
Be careful when implementing logon triggers, if not coded properly you will be rejecting logins that should be able to login (including your own!). Make sure to test on test/dev environments first.
Keep in mind that this code is executed before the session is created, so system views that rely on the session id (SPID) won't contain the currently checked login until the triggers ends without rollback or high enough failure.
You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using SSMS.
For example:
CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1
ON ALL SERVER FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() = N'SQLLogin1' AND PROGRAM_NAME() LIKE N'Microsoft SQL Server Management Studio%'
BEGIN
RAISERROR('Direct connection by SSMS refused.', 16, 1)
ROLLBACK
END
END
The ROLLBACK
inside the trigger will reject the connection (there's an implicit transaction wrapping the call to the trigger on the logon event).
Be careful when implementing logon triggers, if not coded properly you will be rejecting logins that should be able to login (including your own!). Make sure to test on test/dev environments first.
Keep in mind that this code is executed before the session is created, so system views that rely on the session id (SPID) won't contain the currently checked login until the triggers ends without rollback or high enough failure.
answered 7 hours ago
EzLoEzLo
2,2661420
2,2661420
thank you! question - if I make any mistake in logon trigger, and it blocks even sysadmin account from logging in, is there still a way to get into SQL Server and disable logon trigger ?
– voodoo_sh
7 hours ago
2
You can drop the trigger without it firing if you connect with a DAC (dedicated administrator connection). It's a particular single-user connection you can issue against the server whenever things go wrong. It's usually used directly with sqlcmd, but you can do it with SSMS too. docs.microsoft.com/en-us/previous-versions/sql/…
– EzLo
7 hours ago
3
This will work for a few minutes, until the developer uses a different tool. You simply cannot keep a good developer out if they know a login with permissions.
– Joe
6 hours ago
This is more an policy solution than a security solution. IE the logon trigger makes it clear that it's against policy to directly connect to the production database. And since it's unlikely you can protect against a actually malicous developer anyway, that may be good enough.
– David Browne - Microsoft
6 hours ago
@voodoo_sh: Be prepared to find a backdoor in the program codebase.
– Joshua
4 hours ago
|
show 4 more comments
thank you! question - if I make any mistake in logon trigger, and it blocks even sysadmin account from logging in, is there still a way to get into SQL Server and disable logon trigger ?
– voodoo_sh
7 hours ago
2
You can drop the trigger without it firing if you connect with a DAC (dedicated administrator connection). It's a particular single-user connection you can issue against the server whenever things go wrong. It's usually used directly with sqlcmd, but you can do it with SSMS too. docs.microsoft.com/en-us/previous-versions/sql/…
– EzLo
7 hours ago
3
This will work for a few minutes, until the developer uses a different tool. You simply cannot keep a good developer out if they know a login with permissions.
– Joe
6 hours ago
This is more an policy solution than a security solution. IE the logon trigger makes it clear that it's against policy to directly connect to the production database. And since it's unlikely you can protect against a actually malicous developer anyway, that may be good enough.
– David Browne - Microsoft
6 hours ago
@voodoo_sh: Be prepared to find a backdoor in the program codebase.
– Joshua
4 hours ago
thank you! question - if I make any mistake in logon trigger, and it blocks even sysadmin account from logging in, is there still a way to get into SQL Server and disable logon trigger ?
– voodoo_sh
7 hours ago
thank you! question - if I make any mistake in logon trigger, and it blocks even sysadmin account from logging in, is there still a way to get into SQL Server and disable logon trigger ?
– voodoo_sh
7 hours ago
2
2
You can drop the trigger without it firing if you connect with a DAC (dedicated administrator connection). It's a particular single-user connection you can issue against the server whenever things go wrong. It's usually used directly with sqlcmd, but you can do it with SSMS too. docs.microsoft.com/en-us/previous-versions/sql/…
– EzLo
7 hours ago
You can drop the trigger without it firing if you connect with a DAC (dedicated administrator connection). It's a particular single-user connection you can issue against the server whenever things go wrong. It's usually used directly with sqlcmd, but you can do it with SSMS too. docs.microsoft.com/en-us/previous-versions/sql/…
– EzLo
7 hours ago
3
3
This will work for a few minutes, until the developer uses a different tool. You simply cannot keep a good developer out if they know a login with permissions.
– Joe
6 hours ago
This will work for a few minutes, until the developer uses a different tool. You simply cannot keep a good developer out if they know a login with permissions.
– Joe
6 hours ago
This is more an policy solution than a security solution. IE the logon trigger makes it clear that it's against policy to directly connect to the production database. And since it's unlikely you can protect against a actually malicous developer anyway, that may be good enough.
– David Browne - Microsoft
6 hours ago
This is more an policy solution than a security solution. IE the logon trigger makes it clear that it's against policy to directly connect to the production database. And since it's unlikely you can protect against a actually malicous developer anyway, that may be good enough.
– David Browne - Microsoft
6 hours ago
@voodoo_sh: Be prepared to find a backdoor in the program codebase.
– Joshua
4 hours ago
@voodoo_sh: Be prepared to find a backdoor in the program codebase.
– Joshua
4 hours ago
|
show 4 more comments
I think there is no reliable solution for your problem since Application Name
is modifiable parameter
that cam be changed by any user.
Here is how to change it within SSMS
:
In Connect to Database Object
dialog choose Options, open Additional Connection Parameters
and choose any name for Application Name
like this:
Now sys.dm_exec_sessions
DMV and Program_name() will show you what you passed in your connection string in Application Name
parameter:
add a comment |
I think there is no reliable solution for your problem since Application Name
is modifiable parameter
that cam be changed by any user.
Here is how to change it within SSMS
:
In Connect to Database Object
dialog choose Options, open Additional Connection Parameters
and choose any name for Application Name
like this:
Now sys.dm_exec_sessions
DMV and Program_name() will show you what you passed in your connection string in Application Name
parameter:
add a comment |
I think there is no reliable solution for your problem since Application Name
is modifiable parameter
that cam be changed by any user.
Here is how to change it within SSMS
:
In Connect to Database Object
dialog choose Options, open Additional Connection Parameters
and choose any name for Application Name
like this:
Now sys.dm_exec_sessions
DMV and Program_name() will show you what you passed in your connection string in Application Name
parameter:
I think there is no reliable solution for your problem since Application Name
is modifiable parameter
that cam be changed by any user.
Here is how to change it within SSMS
:
In Connect to Database Object
dialog choose Options, open Additional Connection Parameters
and choose any name for Application Name
like this:
Now sys.dm_exec_sessions
DMV and Program_name() will show you what you passed in your connection string in Application Name
parameter:
answered 6 hours ago
sepupicsepupic
7,666820
7,666820
add a comment |
add a comment |
You can't cut off a specific client, as already detailed in the other answers.
The solution is to remove the access privileges to the production systems from the developer's accounts.
Any change must be scripted and a dba will run the script.
Deployment is performed by a sysadmin; devs produce a package they give to someone with proper privileges and devs never see the configs used on production systems.
Debugging is arranged on a case by case basis with a copy of the production data in a staging environment as a preferred solution or a temporary account with limited privileges if needed.
add a comment |
You can't cut off a specific client, as already detailed in the other answers.
The solution is to remove the access privileges to the production systems from the developer's accounts.
Any change must be scripted and a dba will run the script.
Deployment is performed by a sysadmin; devs produce a package they give to someone with proper privileges and devs never see the configs used on production systems.
Debugging is arranged on a case by case basis with a copy of the production data in a staging environment as a preferred solution or a temporary account with limited privileges if needed.
add a comment |
You can't cut off a specific client, as already detailed in the other answers.
The solution is to remove the access privileges to the production systems from the developer's accounts.
Any change must be scripted and a dba will run the script.
Deployment is performed by a sysadmin; devs produce a package they give to someone with proper privileges and devs never see the configs used on production systems.
Debugging is arranged on a case by case basis with a copy of the production data in a staging environment as a preferred solution or a temporary account with limited privileges if needed.
You can't cut off a specific client, as already detailed in the other answers.
The solution is to remove the access privileges to the production systems from the developer's accounts.
Any change must be scripted and a dba will run the script.
Deployment is performed by a sysadmin; devs produce a package they give to someone with proper privileges and devs never see the configs used on production systems.
Debugging is arranged on a case by case basis with a copy of the production data in a staging environment as a preferred solution or a temporary account with limited privileges if needed.
answered 1 hour ago
PaoloPaolo
334212
334212
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231228%2fhow-to-deny-access-to-sql-server-to-certain-login-over-ssms-but-allow-over-net%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
You can use a server logon trigger to force a login failure when connecting to the server. Be careful when implementing one of these, if not coded properly you will be rejecting logins (make sure to test in proper test environment). docs.microsoft.com/en-us/sql/relational-databases/triggers/…
– EzLo
8 hours ago
3
While blocking SSMS might work, it's not a security solution. You can set the
program_name
to anything you want, even in SSMS by settingApplication Name=whatever
in the Additional Connection Parameters. For a security solution, prevent the devs from seeing the passwords for production databases.– David Browne - Microsoft
6 hours ago
3
How would this prevent them from making a console application to run the same update statement? If anything you're making this ugly process even more ugly.
– Deacon
6 hours ago
4
"BUT they work with applications and see connection strings -> they know passwords from some SQL accounts ... that have UPDATE permissions." There's your problem. Have proper account management and don't give them passwords they're not authorized to use. If you don't trust them to handle the account responsibly, then they shouldn't have it.
– jpmc26
5 hours ago
4
I think @jpmc26 is on the money. This sounds like a XY-problem where you shouldn’t worry about SSMS access but about developers having credentials they shouldn’t have. There are ways to handle the secrets so that devs don’t need production credentials - and it sounds like that’s what you are after.
– vhu
4 hours ago