question

jatkin avatar image
jatkin asked

Prevent access to SQL Server 2005 databases from Microsoft Excel and Word

Hi, I am looking for a way to prevent a user from accessing our sql 2005 database through excel and word. Users have a login and password which give them full access to all tables in the database, but they go through a login script where permissions are granted/denied. They would avoid this limitation if connecting from anything other than the login program.

While the proper method may be modifying the users/logins themselves, I am looking for a quick fix, as the first method may take a while to go through all the users and determine what they need access to. I saw something about Logon scripts, but could use some additional guidance. Thank you for your help.

sql-serversecurityexcel
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

As long as the application doesn't use a DSN that is configured on the desktop that the user can configure, try the following.

Configure your DB server to permit connections on a specific port only. Configure the connection string of the application to connect via this port (rather than the standard 1433). Make sure the port is enabled through any routers accessing the server and block 1433 at the routers.

I would then also disable SQL Browser.

Excel and Access shouldn't be able to connect unless your users know the port at this point.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered

On the continuum of fixes, this one comes in at 'fairly quick'

Create a login trigger to prevent logins from access or excel with something like

IF APP_NAME() LIKE '%excel%'
 ROLLBACK

Frederik Vandeputte covers the login trigger solution to your question in greater depth at http://www.vandeputte.org/2006/11/sql-server-logon-triggers-part-2.html

A determined and savvy excel user can modify the Application Name key in the excel connection string or connect with a visual basic module to get around this.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.