Jim Lawrence
jlawrenc1 at shaw.ca
Sat Jan 22 15:32:29 CST 2011
Hi John: Basically, you can limit the users that have access to your SQL server by creating a group of people with the same rights on the hosting server. Start > Administration tools > Computer Management > Local Users and Groups. First create the new users and then create a group and add all the new users. Make sure to add good passwords to each or force them to change upon initial entry, set the strength of the password and the password's duration through your selected policy... minimum 12 characters, mixture of numbers and letters, upper and lower case and additional special characters would be my starting point. Then in the Microsoft SQL Server Management Studio. The appropriate SQL Server > Databases > the database of choice > Security > Users > enter the Group and then set all their access privileges. When completed a certain group of users accessing the MS SQL server, via the sites IP address through port 1433, to the hosting server, will be challenged for their user names and passwords to gain access to a particular database, with limited rights. If the hosting server (the one with the MS SQL server in it) is the only external access point then go into your router, make sure port 1433 is open and (virtual) direct via your internal IP addressing to that hosting server. Note: If you have multiple MS SQL servers running on various servers then you can change the default listening port in each MS SQL. (Can not remember how to do that but it should be easy to look up. Check the following link out) http://www.databasejournal.com/features/mssql/article.php/3689846/Using-Non- Standard-Port-for-SQL-Server.htm This should be all handled from your users FE application. The last items that might be useful is first open notepad, create a text file with any name with an extension of UDL. Then go in an experiment until you correctly connect to your SQL server and when done, change the extension to TXT and cut and paste the displayed link into your users FE application. (Remember if you change the listening port that will have to be appropriately set.) There are few more things I have undoubtedly over-looked or forgotten but that's the basics and I am sure some of the gurus on this site will correct me or fill in the details. HTH Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Saturday, January 22, 2011 8:16 AM To: Sqlserver-Dba; VBA Subject: [dba-SQLServer] How do I troubleshoot SQL Server Security What I want to do: Create an instance of SQL Server running on a specific server, and somehow magically allow a specific set of users logged in to the "system" (not necessarily that server) to access the server. AFAIK Users log in to Active Directory. I do not use Active Directory on my home / business network and do not really understand how it works. In any event it appears that I am going to have to use Active Directory in the SQL Server login? What I tried to do: On the server (windows 2000) I: 1) Created a windows security group DISUsers 2) Added most of the normal users of the computer, all of which will use the SQL Server 2005 Express database, including my user. 3) In SQL Server 2005 Express SSMS at the server level I clicked security login 4) I created a login DiscoServer and I assigned that to the group DiscoSvr\DiscoUsers 5) I checked that the login had the public server role (and only that role) 6) I went into the DISCO database and clickde SDecurity / Users 7) I created a DiscoUsers user assigned to the DiscoSvr\DiscoUsers object (group) 7) I checked the Role members db_DataReader and db_DataWriter I saved all of the above. I then go to my workstation, log in, open ssms and select DiscoSvr\SQLExpress. I can see the database object and I can see a + symbol but if I try to expand the database to see the objects I get a very generic error message "DispecLLC\jcolby is not able to access the database "Disco" under the current Security Context: SQL Server Error 916" I do not understand network security, but AFAICT what is happening is that I am logging in to a network (Active Directory) login (which all users do) but I have just set up SQL Server on that server to use a group / users specific to that server. So is it possible to use a similar method to manipulate SQL Server with active directory groups / users? How is that done. -- John W. Colby www.ColbyConsulting.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com