Questions

SQL Authentication Problem

+
0 Votes
Locked

SQL Authentication Problem

Tonie16
Good day,

I'm a bit new to SQL administration.
We are currently experiencing a problem with our Server Authentication Mode.
We need the Server Authentication to be on Mixed Mode (SQL Server and Windows Authentication mode).

But for some strange reason the SQL Server automatically changes this settings to Windows Authentication only. ??

I've gone through the SQL and Windows Logs, and all that I can see is that it seems like the SQL process itself that changes this setting.

Some info. We run SQL 2005 with SP3. SQL Server is part of a Windows 2003 Domain. SQL Server is a member server.

How can I stop the Server Authentication from changing to just Windows Authentication?

What i've also done now is to enable C2 audit tracing, not sure if this might shed some light on the problem.

Thank you for your help.
  • +
    0 Votes
    BFilmFan

    Verify that it actually is in mixed mode:

    Open the Enterprise Manager.
    Expand a Server group.
    Right-click a server name, and then click Properties.
    Click the Security tab.
    Under Authentication, click the SQL Server and Windows option button.
    Restart SQL Server in order for the change to take effect.

    +
    0 Votes
    Tonie16

    Thank you for your reply.

    I have made the change under Security Tab / Authentication, and even after the restart the authentication is still set to Mixed mode.
    But for some reason every morning around 3am (I think) it changes back to only Windows authentication.

  • +
    0 Votes
    BFilmFan

    Verify that it actually is in mixed mode:

    Open the Enterprise Manager.
    Expand a Server group.
    Right-click a server name, and then click Properties.
    Click the Security tab.
    Under Authentication, click the SQL Server and Windows option button.
    Restart SQL Server in order for the change to take effect.

    +
    0 Votes
    Tonie16

    Thank you for your reply.

    I have made the change under Security Tab / Authentication, and even after the restart the authentication is still set to Mixed mode.
    But for some reason every morning around 3am (I think) it changes back to only Windows authentication.