Monday, 4 September 2017

Difference between authentication methods on sql server

As we all know sql server provides us with two option to authenticate ourselves: Windows Authentication and Sql Server Authentication. Something very interesting i just figured out when i was using Instant File initialization which i wanted to share wit you. So i had a new server and restored the first database, i saw the restore was stuck at 0%, and remembered that id didn't give permission to sql server user to use instant file Initialization. so i went secpol and added the sql user and restarted the restore process which worked like a charm. After some time i restored a second database, and again saw restore was stuck at 0%. Now i was a bit confused, as i already did IFI before, why would restore take so long? Then i had a better look and saw that this time i logged into sql server using windows authentication with my windows credentials, now that means the sql server runs now with my account and not with the sql server account! But on IFI i only added permission to the sql server account and not to my users, so now that sql server runs with my user it doesn't have permission to run IFI and therefore restore process takes longer. So i cancelled the restore job , logged in to sql server with sql authetication and rerun the restore job which worked now nicely. So what we learn here is that when you use windows authentication the sql server will run with your user account and not the sql server user account.

No comments:

Post a Comment