Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    1

    Unanswered: Enabling Mixed Mode Auth. with tSQL?

    Is it possible to run a tSql statement that will enable Mixed-Mode authentication? My customers need to have this set and I don't want them to have to go into Enterprise Mgr. Any ideas?

    Thanks!

  2. #2
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51
    I'm not aware of any way. You may want to run a profiler trace and then do it through enterprise manager to see what command sit executes.

    Just an idea...

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Valid tip by Loach, but that does include reading thru registry keys which is not advisable to deal with a code.
    YOu're better of with GUI supplied tools.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Aug 2002
    Posts
    2

    Re: Enabling Mixed Mode Auth. with tSQL?

    Originally posted by riceman744
    Is it possible to run a tSql statement that will enable Mixed-Mode authentication? My customers need to have this set and I don't want them to have to go into Enterprise Mgr. Any ideas?

    Thanks!
    Hello,
    you can change security model this way


    begin
    DECLARE @hr int
    DECLARE @sqlserver int
    declare @hresult int
    DECLARE @IntegratedSecutiry int
    declare @hostname varchar(255)

    declare @servername varchar(255) set @servername = @@servername

    EXEC @hr = master.dbo.sp_OACreate 'SQLDMO.SQLServer', @sqlserver OUTPUT
    IF @hr = 0
    PRINT 'Ok'
    ELSE
    EXEC sp_OAGetErrorInfo @sqlserver

    EXEC @hr = master.dbo.sp_OASetProperty @sqlserver, 'LoginSecure', 'TRUE'
    IF @hr = 0
    PRINT 'Ok'
    ELSE
    EXEC sp_OAGetErrorInfo @sqlserver


    exec @hr = sp_OAMethod @sqlserver, 'Connect', NULL, @servername
    IF @hr = 0
    PRINT 'Ok'
    ELSE
    EXEC sp_OAGetErrorInfo @sqlserver

    EXEC @hr = master.dbo.sp_OAGetProperty @sqlserver, 'IntegratedSecurity', @IntegratedSecutiry OUTPUT
    IF @hr = 0
    PRINT 'Ok'
    ELSE
    EXEC sp_OAGetErrorInfo @sqlserver


    EXEC @hr = master.dbo.sp_OASetProperty @IntegratedSecutiry, 'SecurityMode', '2'
    IF @hr = 0
    PRINT 'Ok'
    ELSE
    EXEC sp_OAGetErrorInfo @sqlserver


    EXEC @hr = master.dbo.sp_OAGetProperty @IntegratedSecutiry, 'SecurityMode'--, @IntegratedSecutiry OUTPUT
    IF @hr = 0
    PRINT 'Ok'
    ELSE
    EXEC sp_OAGetErrorInfo @sqlserver

    EXEC @hr = master.dbo.sp_OADestroy @sqlserver
    end
    GO

    by
    Paan-cha

  5. #5
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You can do it by modifying the registry.
    For default instance
    HKLM/Software/Microsoft/MSSQLServer/MSSQLServer/LoginMode

    = 1 integrated security, 2 mixed.

    But you probably don't want them to do that either.

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    If you want to go that route then how about:

    Code:
    declare @IntValue int
    exec xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\MSSQLServer','LoginMode',@IntValue OUTPUT
    select @IntValue as LoginMode
    
    exec xp_regwrite 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\MSSQLServer','LoginMode','REG_DWORD',1
    
    exec xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\MSSQLServer','LoginMode',@IntValue OUTPUT
    select @IntValue as LoginMode
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •