locked
Can not connect to sqlserver Authentication Mode in MS SqlServer 2019 RRS feed

  • Question

  • User1839056048 posted

    Iam using sql server 2019.

    I want to connect through sql server authentication mode.

    i can connect throgh windows authentication mode.

    I have connected to sqlserver using windows authentication  and click security and login sa is there.

    i have changed the password of sa and tried to login but it is showing following error

    Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time. (.Net SqlClient Data

    Provider)

    How to solve this

    Regards

    Baiju

    Sunday, August 16, 2020 7:48 AM

All replies

  • User753101303 posted

    Hi,

    The first problem is that SQL Server seems currently started in single user mode (which is used for heavy maintenance operations) as pointed by the error message. See https://www.sqlshack.com/different-ways-to-start-a-sql-server-single-user-mode/

    Is this expected? You may want to restart SQL Server (and possibly undo first this configuration change) to resume normal operation or ask those in charge of SQL Server inside your company.

    Then :
    - by default SQL Server uses Windiows authentication, you may have to enable this: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode?view=sql-server-ver15
    - an application usually doesn't use the sa account but its own account with just the needed permissions

    Sunday, August 16, 2020 8:22 AM
  • User364663285 posted

    Hi,

    Try and use

    -m "Client Application Name"
    
    When you use the -m option with SQLCMD or SQL Server Management Studio, you can limit the connections to a specified client application. For example, -m"SQLCMD" limits connections to a single connection and that connection must identify itself as the SQLCMD client program. Use this option when you are starting SQL Server in single-user mode and an unknown client application is taking the only available connection. To connect through the Query Editor in Management Studio, use -m"Microsoft SQL Server Management Studio - Query".

    Sunday, August 16, 2020 3:36 PM