none
How to Disable Windows Authentication

    Question

  • Hi,

    I want to protect my sql server data with enduser including the End User with Administrative rights. I don't want to expose my sql server data to any one. It is published only through my front end application.


     
    I have installed the Second Instance of SQL SERVER and I dumped my database into that Second Instance.  even from second instance of sql server also through Windows Authentication mode the client is able to connect to the database.

     We are able to stop connecting through SQL Authentication but not with Windows authentication because he may have admin rights on his/her system.

     Is there any solution to stop connecting to the second instance from windows authentication?

     Please suggest me on that.

     Thanks & Regards

    Anji Reddy

     

    Friday, May 20, 2005 7:02 AM

Answers

  • You cannot disable Windows Authentication on SQL Server. In fact, SQL Server recognizes two modes of security: 
          1) Windows Authentication
          2) SQL Server AND Windows Authentication

    There's a way out, however. Users of the Adminstrators group of Windows are able to connect to the SQL Server because there's a login account in SQL Server for it and is assigned the System Administrators role. You'll need to delete that login. But before you do so, please be sure that 
          1) the security mode is set to SQL Server and Windows, and
          2) you know the password of SQL Server's sa login
    Otherwise, you'll render your SQL Server inaccessible!

    To do so, follow these steps:
          1) Open SQL Server Enterprise Manager
          2) Expand the nodes to reveal your SQL Server instance
          3) Expand your SQL Server instance node to reveal the various nodes
          4) Expand the Security node and click Logins
          
    5) Right-click the BUILTIN\Administraors login and click Delete, click Yes 
              when prompted for confirmation

    No one from the Administrators group will be able to access your SQL Server now. Hope it helps you.

    Regards,

    Saturday, May 21, 2005 11:48 AM

All replies

  • You cannot disable Windows Authentication on SQL Server. In fact, SQL Server recognizes two modes of security: 
          1) Windows Authentication
          2) SQL Server AND Windows Authentication

    There's a way out, however. Users of the Adminstrators group of Windows are able to connect to the SQL Server because there's a login account in SQL Server for it and is assigned the System Administrators role. You'll need to delete that login. But before you do so, please be sure that 
          1) the security mode is set to SQL Server and Windows, and
          2) you know the password of SQL Server's sa login
    Otherwise, you'll render your SQL Server inaccessible!

    To do so, follow these steps:
          1) Open SQL Server Enterprise Manager
          2) Expand the nodes to reveal your SQL Server instance
          3) Expand your SQL Server instance node to reveal the various nodes
          4) Expand the Security node and click Logins
          
    5) Right-click the BUILTIN\Administraors login and click Delete, click Yes 
              when prompted for confirmation

    No one from the Administrators group will be able to access your SQL Server now. Hope it helps you.

    Regards,

    Saturday, May 21, 2005 11:48 AM
  • Hi
    Thank you very much
    Yes absolutely your answer helpful for me.

    Thanks a lot
    Anji reddy
    Monday, May 23, 2005 11:23 AM
  • Thanks a lot.
    • Proposed as answer by veeramanink Monday, March 01, 2010 4:05 AM
    Monday, March 01, 2010 4:04 AM
  • This is a great solution, but I think the user will still be able to copy the .mdf and .ldf files and attach them to any other server!, is there a way to prevent them also from copying the files?
    Saturday, May 08, 2010 2:53 PM
  • Yes, you can prevent a user from copying the .mdf and .ldf files as well as the backups, by designing a security structure to prevent him from being able to access the files.   Protection of the server and files should always be part of the overall security strategy.  

    Likewise, this means preventing any unauthorized person from getting physical access to the server, such that they could remove the disks and take them to another machine.

    SQL Server has encryption mechanisms, which made stolen data harder to use, but first protect the files.

    RLF

    Saturday, May 08, 2010 7:42 PM
  • This is a great solution, but I think the user will still be able to copy the .mdf and .ldf files and attach them to any other server!, is there a way to prevent them also from copying the files?


    This can be overcome by TDE of SQL server 2008 enterprise edition. Some of the articles on TDE from our website

    http://sql-articles.com/articles/security/transparent-data-encryption-tde-sql-server-2008
    http://sql-articles.com/articles/security/implementing-transparent-data-encryption-tde
    http://sql-articles.com/articles/security/how-to-copy-move-a-database-that-is-encrypted-with-tde


    Thanks, Leks
    Saturday, May 08, 2010 11:49 PM