I just received following email from one of the blog reader.
Question : “How to disable Windows Authentication?”
Answer : It can not be disabled. Windows Authentication is the most secure way to login in system.
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
23 Comments. Leave new
How i can Password on sqlserver DataBase Similar Oracle?
Technically by disallowing Windows users access you can block anyone’s connection in effect disabling it.
While being the most secure, its not the fastest…for sites that have hundreds/thousands of connections made per minute there is a noticable difference between Windows and SQL Server Authentication.
How to implement database clustering in SQL Server 2005..Kindly let me know the steps for it…Does it really benefits the organisation….
When I open ALM application (Automation license managment) I get error ” Login in database is momentarily out of service”
How can I remove this warning and start application normally.
As above it is said “disallowing Windows users access you can block anyone’s connection in effect disabling it.”
how can we do it
Hi,
can u give me main difference between function and trigger?
please let me know ?
Thanks!
Hello Sir,
Can you tell me how can I protect my database with password. I do not want user to operate on database without password.
Is there any way to disable Windows Authentication or any way to protect database with password.
I think there should be some provision to protect database.
I have database on Win XP system and there’s no domain system at my client side. I just want to protect my database. I dont want anybody to view data without my permission.
Thanks a lot for lots of previous posts.
I hope you will give me a ultimate solution for this too..
Thankx again.
Hello Ishrar,
Use “SQL Server and Windows Authentication mode” as server authentication. Then do not create any login for any windows user. You can also remove BuiltinAdministrator group.
This way you there would be only SQL logins though which user would login in your database.
Regards,
Pinal Dave
Pinal Dave,
I note your comment of March 29 and I have been trying to remove the BUILTIN\Administrators and the BUILTIN\Users groups but no success. I have tried both DROP LOGIN and ALTER LOGIN … DISABLE but I get a syntax error message. Using SQL Server Express 2008.
Can you help?
Jeremy
OK, got it now. Needs brackets around, e.g. DROP LOGIN [BUILTIN\Adminstrators]. Apologies.
J.
Thank you very much,
I have tried and I got what I needed to do.
Reading your posts from last 2 years…
Thanks again.
Thanks Pinal.. Your blog always helps me.. You Replied me 4 years back. Today again I need same thing to be done using Command.. I want to remove Windows Authentication using Command.. The reason is, My clients have their databases on their server but I dont want them to access my Application’s database. I use to create new User XYZ with password which is known by my application only. And I use to remove Windows Authentication From SSMS. Sometimes I dont have SSMS installed, And I don’t want to install (for security purpose). How can I remove Windows Authentication using Command.
Thanks again…
I am very confused in Database Backup restore because when I restore from SSMS choosing which database I am replacing, why still I have to mention which file should be restored.. Actually do replace the file name but I must know the reason how and why it works like this.
Regarding SQL Server security, We have a 3rd party app, Costpoint from Deltek, which users login to access and update our 2005 SQL Server database. I’ve been tasked to disable an account after 3 invalid login attempts through this application to the database. My boss doesn’t want me to go the route of the alter login command on the database side, and I’m not allowed to update the application code either. Is there a way to do this using SQL Server authentication?
Hi Matt,
I face the same issue.
Did you find a solution to this?
Thank you,
Dave
to disable window authentication login, you must collapse the “Security>Server Roles” part under the list of your databases. Right Click the ‘public’ role and select properties. select ‘Permissions’ under ‘General’ and then tick the ‘Deny’ CheckBox for the ‘Connect’ permission.
All users using window authentication only that would try to connect to the server would be blocked because BUILTIN\USERS have public permissions only.
Regards,
Ross
How to protect database, by login through static IP from out side the network using sQL management studio. But application should be run from that location. Application is accessing database
USE [master]
GO
ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
EXEC master..sp_dropsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’sysadmin’
GO
Login with SA and run above query to disable SQL SERVER – Windows Authentication.
Hi Pinal,
I changed my xp machine password and since than My sql server express (2008) windows authentication is not working through .net.
I created one udl and test windows authentication. test is fine but its shows me only three databases Master, MsDB and TempDB.
How can i Fix this?
Regards,
Saily
Hello Pinal Dave
in MS Access , if you secure the db with password , you can’t open this db without password even if you copy this db to another PC , or you can’t open it with windows authentication !!
Is there way to secure my db in SQL Server with password and no one can open it even if he copy it to another PC or using windows authentication ??
Open SQL Management Studio. select SQL authentication mode and logging (which have full credential rights). in Object Explorer, Security->Login. Right click on [BUILTIN\Users] and select “propeties”. go to “Status” and select “disable” login. find your windows authentication users and apply job same as above.
It works for me. now I can only connect with SQL server authentication mode.
How to disable windows authentication in sql server 2005 ? only Open server mode?
Hai Sir,
I am using Sql Server 2008 R2 version,I have some problem in that,please clear my problem.I created one database in the “server authentication” mode,then i save it.if i disconnect the “server authentication” and go to “windows authentication” and open means i can able o see that database an table(that is my problem).
i want solution for this(i can create database in “server authentication” mode but in “windows authentication” no one see that)(if any option to do this)..
Regards,
kutti
Hi Pinal,Hi, Am quite new to SQL Server. I see that SQL Server has 3 auth modes :
1. SQL Server login credentials
2. Windows Auth
3. Mixed (1 or 2)
Which is more commonly used in today’s world in firms?
Sir,
Is there any way to know the list of all inactive Windows users in the AD thru the SQL query ?
I want to list out all the users(Windows) who has the access level permission on the SQL Server but they can’t enter into the SQL Server as they are blocked or their account disabled in OS Level.