Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Is it possible to allow a stored procedure to be called only from a particular application or from an application running from a specific location?

Posted on 2012-03-14
5
Medium Priority
?
346 Views
Last Modified: 2012-03-28
I have a database on a MSSQL 2008 server that has several stored procedures.  The only way of interacting with the data in the tables is through those stored procedures.  Is there any way to restrict access to the stored procedures to applications that run from a particular location - i.e. a specific network share?  A large number of users need access to the data, but we would like to tightly control how they access it.
0
Comment
Question by:CousinDupree
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 37721842
I suggest use SQL Database role security and add one DB role for your app like application_users then grant all EXEC rights on the stored proc(s) and/or dependent UDF functions plus SELECT,INSERT,UPDATE,DELETE to all tables/views and objects to linked to this SP.

Then you add only SQL or NT logins to application_users comming from that network segment so nothing else can access that SP other than SA.
0
 

Author Comment

by:CousinDupree
ID: 37722018
How would I go about adding only SQL or NT logins that come from a particular place? When you say 'particular network segment', what exactly are you referring to?
0
 
LVL 40

Expert Comment

by:lcohan
ID: 37722141
"Is it possible to allow a stored procedure to be called only from a particular application or from an application running from a specific location?"

Well I guess my answer is direct for the first part of your question as that "particular application " should (idealy) have its own login to the database right? If you impersonate users then you would add all those users to the new application_users database role (you can call it whatever you want) and only they can run that SP right?

As far as network segments and IP trafic you can selectively block (incomming/outgoing) port 1433 (or whatever port you use for your SQL) traffic in the firewall right?
0
 

Author Comment

by:CousinDupree
ID: 37722397
I see, your intent was to use the firewall to control access to the SQL server.  My ultimate goal is to allow users to access the data through an application that calls the stored procedures, but to prevent a programmer from writing an unapproved application that can be used to access the data.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 37726413
You could find more detail online if you search and download:

SQL_Server_2008_R2_Security_Best_Practice

and from links below:

For SQL Injection Attacks on IIS Web Server, you could refer to:
 http://blogs.iis.net/bills/archive/2008/04/25/sql-injection-attacks-on-iis-web-servers.aspx
 http://www.4guysfromrolla.com/webtech/061902-1.shtml
 
About secure login, you could refer to:
 http://www.sql-server-citation.com/2009/05/how-to-secure-sql-server-sql-server.html
 
For how to secure IIS web server using SSL, you could refer to:
 http://msdn.microsoft.com/en-us/library/ff649205.aspx
 http://msdn.microsoft.com/en-us/magazine/cc301946.aspx
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question