locked
sql server 2012 windows authentication mode but serverproperty isintegratedsecurityonly returns 0? RRS feed

  • Question

  • Hi we have a sql server 2012 r2 where we have set authentication mode to Sql server and windows authentication mode.

    

    but when I query the server property

    WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0 THEN 'Not Integrated security'

    I get

    My understanding is that "Sql server and windows authentication mode' is 'integrated security' do I have this wrong?

    Sunday, February 28, 2016 9:02 PM

Answers

  • But not "only".

    You always have integrated security. Since SQL 7, there has been no way to turn that off. SQL authentication is optional. Windows authentication is not.

    • Marked as answer by bobghw Monday, February 29, 2016 1:11 AM
    Sunday, February 28, 2016 10:03 PM

All replies

  • But not "only".

    You always have integrated security. Since SQL 7, there has been no way to turn that off. SQL authentication is optional. Windows authentication is not.

    • Marked as answer by bobghw Monday, February 29, 2016 1:11 AM
    Sunday, February 28, 2016 10:03 PM
  • wondered about that 'Only' 

    thanks for clearing that up.

    Monday, February 29, 2016 1:12 AM
  • In fact, there was never a way to turn off Windows Authentication - i.e., no "SQL authentication only". The three (legal) values in the registry weren't that easy to understand. Or rather, "Windows only" is/was obvious, but the two other weren't easily explained to novice DBAs (as a trainer). I have to dig deep to recall the difference between 2 and 0, so it is easier to point to a blog post I wrote a few years ago.

    Note: This is utterly useless reading! :-)

    http://sqlblog.com/blogs/tibor_karaszi/archive/2010/09/15/watch-out-for-old-stuff.aspx


    Tibor Karaszi, SQL Server MVP (Web Blog)


    • Edited by TiborKMVP Monday, February 29, 2016 8:33 PM Edit, changed from "not way to turn off SQL authentication" to "turn off windows authentication". D'uh...
    Monday, February 29, 2016 8:32 PM