Windows Authentication mode

Question

Windows Authentication mode

shirish_padekar

SSC Eights!

Points: 830
More actions
December 16, 2015 at 1:59 am

#314250

We have SQL Server 2012 and I found that we were using sa user in our application's connectionString. Now, we are thinking to replace it with Windows Authentication like below:
connectionString="Data Source=myserver;Initial Catalog=mydb;Integrated Security=True;Max Pool Size=300"
1. Is the above string fine considering security aspect ?
2. In "Security" option of SQL Server's "Properties" page, I see that "SQL Server and Windows Authentication mode" option is checked, should I change that to "Windows Authentication mode" option to make the above change ?
3. Also, we are thinking to disable sa user, but on clicking sa user I can see only delete or rename option. How can we disable sa user? Is this change recommended and can this cause any issue as we have few jobs running under sa user ?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 1

shirish_padekar (12/16/2015)
We have SQL Server 2012 and I found that we were using sa user in our application's connectionString. Now, we are thinking to replace it with Windows Authentication like below:
connectionString="Data Source=myserver;Initial Catalog=mydb;Integrated Security=True;Max Pool Size=300"
1. Is the above string fine considering security aspect ?
2. In "Security" option of SQL Server's "Properties" page, I see that "SQL Server and Windows Authentication mode" option is checked, should I change that to "Windows Authentication mode" option to make the above change ?
3. Also, we are thinking to disable sa user, but on clicking sa user I can see only delete or rename option. How can we disable sa user? Is this change recommended and can this cause any issue as we have few jobs running under sa user ?

1 -Yes that connection string will use windows authentication

2 - No you don't need to make that change for the connection string to work. Only change to Windows Authentication mode if you have no users using SQL logins or jobs configured to run under SQL logins - you should review fully before changing that option.

3 - You can disable the sa user by going into the properties, under the status page is the option to disable or enable. You can also use ALTER LOGIN sa DISABLE;

Any jobs running under sa will fail so you will need to change these to another login before disabling the sa login.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

shirish_padekar (12/16/2015)
We have SQL Server 2012 and I found that we were using sa user in our application's connectionString. Now, we are thinking to replace it with Windows Authentication like below:
connectionString="Data Source=myserver;Initial Catalog=mydb;Integrated Security=True;Max Pool Size=300"
1. Is the above string fine considering security aspect ?

It's fine

2. In "Security" option of SQL Server's "Properties" page, I see that "SQL Server and Windows Authentication mode" option is checked, should I change that to "Windows Authentication mode" option to make the above change ?

You don't need to do that in order to make that connection string work. If you do that you lose the ability to use SQL Logins. Just test this on a non-production system to be sure you're not breaking things. Also, make sure you have an AD group or AD login set up as system admin before you make that switch or you won't be able to get control of the server again. Test all this carefully. Please.

3. Also, we are thinking to disable sa user, but on clicking sa user I can see only delete or rename option. How can we disable sa user? Is this change recommended and can this cause any issue as we have few jobs running under sa user ?

You can't really remove the 'sa' account or change its privileges. You can issue a DISABLE command to the account, AFTER, you set up an AD account as sys admin.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software