Ggthjy

Inventor that creates machine that grabs man from future

Why do members of Congress in committee hearings ask witnesses the same question multiple times?

How to visualize a Cayley graph in this style?

How do we edit a novel that's written by several people?

Visualize execution of scripts

Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?

What are alternatives to Razl?

Where is this triangular-shaped space station from?

Meth dealer reference in Family Guy

How to avoid being sexist when trying to employ someone to function in a very sexist environment?

How can I get the count of how many times a string appears in my list?

Why is commutativity optional in multiplication for rings?

How can I handle a player who pre-plans arguments about my rulings on RAW?

If a druid in Wild Shape swallows a creature whole, then turns back to her normal form, what happens?

How to properly claim credit for peer review?

Should the .gitignore include an entry for .vscode when using Git and VSCode

Can the Grease spell force multiple saves?

Contradiction with Banach Fixed Point Theorem

Is there any relevance to Thor getting his hair cut other than comedic value?

What do the pedals on grand pianos do?

Returning to Programming after 6 years. A little lost on how to start brushing up, what to focus on

Sometimes a banana is just a banana

Why can I easily sing or whistle a tune I've just heard, but not as easily reproduce it on an instrument?

Why is this code uniquely decodable?

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

2

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

share|improve this question

edited 46 mins ago

EzLo

2,2281420

asked 2 hours ago

voodoo_shvoodoo_sh

1737

• 
  
  
  

  
  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
  2 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @EzLo does logon trigger has the ability to disallow SSMS login, but allow login over other "program_name" ?
  
  – voodoo_sh
  1 hour 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 mins ago
  

  
  
  
  

add a comment | 

2

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

share|improve this question

edited 46 mins ago

EzLo

2,2281420

asked 2 hours ago

voodoo_shvoodoo_sh

1737

• 
  
  
  

  
  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
  2 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @EzLo does logon trigger has the ability to disallow SSMS login, but allow login over other "program_name" ?
  
  – voodoo_sh
  1 hour 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 mins ago
  

  
  
  
  

add a comment | 

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

share|improve this question

edited 46 mins ago

EzLo

2,2281420

asked 2 hours ago

voodoo_shvoodoo_sh

1737

share|improve this question

edited 46 mins ago

EzLo

2,2281420

asked 2 hours ago

voodoo_shvoodoo_sh

1737

share|improve this question

edited 46 mins ago

EzLo

2,2281420

asked 2 hours ago

voodoo_shvoodoo_sh

1737

edited 46 mins ago

EzLo

2,2281420

edited 46 mins ago

EzLo

2,2281420

asked 2 hours ago

voodoo_shvoodoo_sh

1737

asked 2 hours ago

voodoo_shvoodoo_sh

1737

2 Answers
2

active

oldest

votes

4

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.

share|improve this answer

answered 56 mins ago

EzLoEzLo

2,2281420

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  48 mins 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
  42 mins ago
  
  
  

  
  
  
  

add a comment | 

1

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:

share

answered 8 mins ago

sepupicsepupic

7,576819

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
});


}
});

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

4

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.

share|improve this answer

answered 56 mins ago

EzLoEzLo

2,2281420

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  48 mins 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
  42 mins ago
  
  
  

  
  
  
  

add a comment | 

4

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.

share|improve this answer

answered 56 mins ago

EzLoEzLo

2,2281420

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  48 mins 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
  42 mins ago
  
  
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered 56 mins ago

EzLoEzLo

2,2281420

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

share|improve this answer

answered 56 mins ago

EzLoEzLo

2,2281420

answered 56 mins ago

EzLoEzLo

2,2281420

answered 56 mins ago

EzLoEzLo

2,2281420

1

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:

share

answered 8 mins ago

sepupicsepupic

7,576819

add a comment | 

1

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:

share

answered 8 mins ago

sepupicsepupic

7,576819

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:

share

answered 8 mins ago

sepupicsepupic

7,576819

share

answered 8 mins ago

sepupicsepupic

7,576819

answered 8 mins ago

sepupicsepupic

7,576819

answered 8 mins ago

sepupicsepupic

7,576819