Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Securing SQL Server 2K (VB6/SQL Server 2K)

    I have an application I wrote in visual basic that loggs data to an SQL Server database that I created with an Access ADP project. This application is widely used now (which I didn't think it would be so popular) and am finding a need to secure this data on the server. I am unfamiliar with administration issues related to SQL Server. There are several databases used by other applications and no one as of yet has password protected the sa user name.

    In my VB app, I don't need the users to authenticate to the front end but I don't want them to just be able to look at everyone elses data on the server. I can therefore create a connection using an administrator user name and password that is hidden from the user. The frist step, I'm assuming, is to put a password on the SA user name - but I'm not sure what that will do to these other applications that don't require a log in.

    Is the SA user name different for each individual application? Meaning if I put a password on my database it wont affect the others?

    What is the proper way to secure all objects in the database so that I can employ an administrative connection to write data to this database (the users will never need to read from it, it simply used for logging) ?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Securing SQL Server 2K (VB6/SQL Server 2K)

    <P ID="edit" class=small>(Edited by WendellB on 09-Jan-03 05:49. add decision on moving thread)</P>First of all, I'm not sure we want to leave this post in the VB/VBA forum. I'll discuss with the other mods, and we may end up moving it to the Access forum, since ADPs are of interest in Access land. If we do move it, we will replace it with a locked thread in this forum. <font color=blue>After discussion, we decided to leave it here - Wendell</font color=blue>

    SQL Server security is about the same complexity level as Access User Security for MDB databases, but works on somewhat different principles. In particular, it has a security model that can be integrated with Win NT logins, so that users don't have to worry about logging in to SQL Sever each time they use a database. It all happens under the sheets, and the connection process is simplified. That presumes that you want to know which user did what, and other such things.

    To begin to secure a SQL Server database, you do need to put a password on the SA account as the first step. That will immediately require that you login for each connection from any other front-end to any database running under that installation, even if it is in a different SQL Server database. So you do want to proceed with some caution, and make sure that all apps are preparred to deal with the SA password. The real issue is to determine how important the data is to your mission. If it's critical, then you really need to lock things down - there are a number of known security vulnerabilities with SQL Server 2000 that need to be dealt with. On the other hand, if it is something that would be just an annoyance if someone hacked in and modified or deleted some of the data, then simply putting a password on SA may be enough.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Securing SQL Server 2K (VB6/SQL Server 2K)

    Wendell, I wanted to get back to you to thank you for your input. I will set up a test SQL Server that I will use for development where I can test my code, specifically the authentication pieces, and put it on the server with authentication turned off. Once we clean up our production SQL Servers and secure the other databases on then I can migrate my application over with all of the security enabled.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Securing SQL Server 2K (VB6/SQL Server 2K)

    Your message is very timely - I presume you are aware of the recent DOS like attack that occurred over the past weekend where SQL Server 2000 installations were the target. Either make sure that your development server isn't accessible from the Internet, or be sure to apply all the latest service packs and security patches from Microsoft. There are a number of sites that are specifically focused on SQL Server security so you may want to visit some of them.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •