• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Security for SQL and Web Connections

I currently have a Public facing Web server that has multiple Web sites two of the Web sites need access to the SQL Database Server on my local network. Currently I have a firewall rule that allows the web server to communicate to the SQL Server through the SQL ports but this is managed by the IP addresses of the SQL Server and The Web Server. The problem with is the web server has access to the complete SQL Server on the ports open for SQL. Is there a way to have individual web sites only have access to individual database on the SQL SERVER?

Thanks
0
ahmad1467
Asked:
ahmad1467
2 Solutions
 
Leon KammerCommented:
Hi,

Using SQL authentication, the individual databases can be locked down to a specific user.

Cheers

Leon
0
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Of course, they are called connection strings where you define a username/ password database etc.
This sql user only has rights to the database defined, nothing else.
0
 
ahmad1467Author Commented:
Thanks
 I under that I can lock down users to individual DB but I guess what I’m really looking for is to lock down Individual Websites to individual Databases.

Thanks
0
 
Leon KammerCommented:
Hi,

you can create a user, set the user's permissions in the DB, and then alter the connection string within the website to connect to the SQL server.

This is generally housed in the web.config file.
0
 
Surendra NathTechnology LeadCommented:
Yes, You can....

in order to accomplish this you have to create two SQL Logins
Let us say ABC, XYZ are the login names.....

Now for ABC assing the DB Reader role in the Database ABC_DB and remove the DBReader role in XYZ_DB....

and do viceversa for XYZ.... this will accomplish what you are looking for

you can use SP_dropRole, and SP_addRole inorder to accomplish this

http://technet.microsoft.com/en-us/library/ms189121.aspx
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now