-1

I created an AD user through Administration tool then performed following commands:

-- Add AD user EX. thu1 to database master

USE [master]
GO
CREATE LOGIN [QALAB\ABC1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

-- Add AD user to database test_db

USE [test_db]
GO
CREATE USER [QALAB\ABC1] FOR LOGIN [QALAB\ABC1]
GO

-- Add AD user role in database

USE [test_db]
GO
ALTER ROLE [db_datareader] ADD MEMBER [QALAB\ABC1]
GO

Now I expect to login to SQL Server using QALAB\ABC1, but can not. I can't see this user under windows authentication. I tried active directory with password option as well but still no success.

MDCCL
  • 8,520
  • 3
  • 30
  • 61
TheBeginner
  • 167
  • 1
  • 2
  • 9
  • So what happens? Do you get an error message? If you do, edit the question and add it. Have you looked at Sql Server's log file for details? – vonPryz Apr 16 '18 at 09:19
  • 3
    Could you please explain how you are trying to login to your database? Which tool / program / CLI are you using? What options does that tool/program provide? Add as much information as possible to your question by editing it again. We might be able to help you if you tell us how you are trying to login using a Windows account. P.S. You can't login using a different Windows account other than what you are currently logged in as, unless you start the program with "Runas different user..." and then provide different Windows credentials. – John K. N. Apr 16 '18 at 09:30
  • 2
    Active Directory with Password is for Azure cloud only. Azure Active Directory Authentication is a mechanism of connecting to MicrosoftAzure SQL Database by using identities in Azure Active Directory (Azure AD). Use this method for connecting to SQL Database if you are logged in to Windows using credentials from a domain that is not federated with Azure, or when using Azure AD authentication using Azure AD based on the initial or the client domain. Details here. – SqlWorldWide Apr 16 '18 at 16:01
  • If you can please post a screenshot, that could answer many questions such as the full error and client in one pic. – Ali Razeghi - AWS Apr 16 '18 at 16:32
  • 1
    To perhaps clarify @hot2use 's point: You must be logged into the Windows system as user QALAB\ABC1 in order to connect to SQL Server as that user. You cannot (for example) start SQL Server Management Studio, select Windows Authentication, and then enter the Windows username and password you want to use - SQL will use the Windows username used to launch SSMS to connect, and those fields should be left blank when using Windows Authentication. – RDFozz Apr 16 '18 at 21:40

2 Answers2

0

Well, we really need more information, but a couple things you can do to troubleshoot

  1. Check the SQL Server logs to see if there are any authentication failures. That will let you know if the user is even connecting to the instance.
  2. Checking the log may also reveal that you are actually connecting using SQL authentication and not windows authentication. As RDFozz points out, the process you are trying to connect to the instance with has to be running under the context of that user. You can do this either by loging in to a windows session using the windows account and starting the process (or at least use runas.exe to start the process as that user).
  3. Create a SQL Login instead, with the same username and password, and if you are able to connect (from whatever application) then you know it's not using windows authentication. Doesn't mean it can't, just mean it's not. (and to fix that we'd definitely need more info).
Yuri
  • 91
  • 2
-2

"Execute AS User" will work for your scenario.

Check this:

https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-2017

Krishna Sriram
  • 7
  • 1
  • Welcome to DBA.SE. We appreciate your contribution/participation. However, the community does expect a certain quality in the answers posted. Please consider reading the following article: How do I write a good answer? (Help Centre) In the section Answer the question there is a short sentence which reads: Links to external resources are encouraged, but please add context around the link so your fellow users will have some idea what it is and why it’s there – John K. N. Sep 13 '18 at 14:33