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?

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.
CousinDupreeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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
CousinDupreeAuthor Commented:
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
lcohanDatabase AnalystCommented:
"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
CousinDupreeAuthor Commented:
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
lcohanDatabase AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.