locked
Seeing unusual behavior signing into Azure SQL RRS feed

  • Question

  • Hello,

    I have used the following T-SQL statements to create a Schema user in my Azure SQL database -

    CREATE LOGIN AppMonitorLogin WITH password='<MyStrongPassword>';  --[in Master DB]
    CREATE USER AppMonitorUser FROM LOGIN AppMonitorLogin; --[from here on in MYDB]
    CREATE SCHEMA AppMonitoring;

    CREATE ROLE [AppMonitoring_ROLE]
    GO
    ALTER AUTHORIZATION ON SCHEMA::[AppMonitoring] TO [AppMonitoring_ROLE]
    GO
    GRANT CREATE TABLE TO [AppMonitoring_ROLE]
    GO
    GRANT CREATE VIEW TO [AppMonitoring_ROLE]
    GO
    GRANT CREATE PROCEDURE TO [AppMonitoring_ROLE]
    GO

    -- Add an existing user to the new role created 
    EXEC sp_addrolemember 'AppMonitoring_ROLE', 'AppMonitorUser'

    And when I am trying to use SSMS to connect to my database server using this user, I get this -

    TITLE: Connect to Server

    ------------------------------

    Cannot connect to MyServer.database.windows.net.

    ------------------------------
    ADDITIONAL INFORMATION:

    Login failed for user 'AppMonitorUser'. (Microsoft SQL Server, Error: 18456)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Any idea why this may be happening?

    Wednesday, February 12, 2020 12:04 AM

All replies

  • Hi Binoy-Sankar,

    Your issue is related to Azure SQL database. I suggest you open a thread in Azure SQL database forums, People there will help you more effectively. 
    Hope this could help you.

    by the way, for SQL connection error 18456, please check if below links could help you.

    How To Fix Login Failed for User Microsoft SQL Server Error 18456 Step-By-Step
    Troubleshooting Microsoft SQL Server Error 18456, Login failed for user

    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, February 12, 2020 2:09 AM
  • Is it single SQL Azure DB? Right click server name from the SSMS and go to server properties. Click security and enable Wndows and SQL Server Authentication mode. 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 13, 2020 6:38 AM
  • Uri,

    Where in SSMS can I go to enable both of those Authentication modes? I don't get "Properties" after right clicking on the DB server name. Screenshot below -


    Tuesday, February 18, 2020 5:21 PM
  • I was trying to login using the database username and not the LOGIN Name.

    I should have used AppMonitorLogin to get in, not AppMonitorUser. My mistake...

    Thanks go to everyone who tried to help.

    Wednesday, February 19, 2020 4:29 PM
  • I was trying to login using the database username and not the LOGIN Name.

    I should have used AppMonitorLogin to get in, not AppMonitorUser. My mistake...

    Thanks go to everyone who tried to help.

    Hi  Binoy-Sankar,

    I am so glad to hear that you have resolved your issue. Please mark your solution as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, February 21, 2020 10:21 AM