Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2006
    Posts
    4

    Question Unanswered: Windows Authentication and EM/QA/ISQL/Office

    Hi,

    A while ago we changed from Sybase to MS Sql Server 2000. Thereby we also introduced Windows Authentication instead of SQL Logins. When end-users wanted to read directly (tools like Enterprise Manager/Query Analyzer/ isql and Office products (like Access and Excel via MS Query)) from the Sybase database we managed that with a SQL login with read-only rights. With Windows Authentication in MS Sql Server 2000 the end-user can enter the database with update rights. Is there a way to manage tools like EM/QA/isql/Office products etcetera so that the end-user can approach the database only with a SQL Login instead of Windows authentication ? Although we are building tooling to kill every user who approaches the database with the tooling mentioned, we think this is not a proper way to manage database access.
    We hope someone has encountered the same problem and knows how to handle this and can help us.

    Greetings,

    Ferrie

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    You could individually set each and every user 's permissions via the sql login, but this might work out better for you:

    1. Use windows to create user groups such as "Accounting Read Only" and add the users to the proper group(s).
    2. In SQL Server, allow the group access to SQL Server in the appropriate database(s).
    3. Set the group permission to datareader and deny datawriter in the database(s) you have allowed the group to access.

    Wash, rinse, and repeat as needed.

    Look up 'Windows NT Groups' in BOL.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jun 2006
    Posts
    4

    Exclamation

    Hi Tom,

    Thanks for your answer. The problem is that users need update rights via tooling build with VB/Uniface/C#. But they can also work via tooling like EM/QA etcetera. Your proposed solution will therefore not work.

    Greetings,

    Ferrie

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    In that case, you have your apps login as an application and give the app update rights. Your users can still use the sql server tools and only have update rights. Look up roles-sql server in BOL and go to the application items.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Jun 2006
    Posts
    4

    Question Re

    Hi Thom,

    We also wanted to introduce application roles. The problem however is that passwords are hardcoded (also encrypted) and we do not do anything with the application developed. The developer (it's just the way it is in our company) has to know passwords to introduce the application role. This is according to our companies security lines forbiden. Furthermore end-users has to be traced when they are logged in. This can be solved in the application but this will take to much effort.

    Greetings,

    Ferrie

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your company is suffering from an archaic security policy. That sort of policy worked fine ten years ago, but it isn't adaptable to modern system security.

    You have a few choices that will allow you to work within the constraints that your company's policy imposes.

    The intuitively obvious answer would be to have the application read the encrypted application role password from the registry, decrypt it, then pass it to the SQL Server. This allows you to change the password easily, and separates the developer from direct knowledge of the password. You can meet the letter of the requirements, ignoring the fact that you've only placed a trivial barrier in front of the developer that will take them seconds to crack if they choose to.

    You can make the application signature part of the server security. This again trivializes the effort needed for the developer to work around the security, and won't be much harder for the power users to crack, but it does appear to meet the letter of the company's requirements.

    You could also have the application impersonate another Windows login. This is acceptable practice, and brings many other potential benefits, but it almost certainly will violate the company's stated security policy.

    My condolences, you are in a bad position.

    -PatP

Posting Permissions

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