AD User Connection test?

Question

AD User Connection test?

n00bDBA

SSCrazy

Points: 2986
More actions
March 31, 2012 at 5:38 pm

#266478

Hello,
If I create a SQL Login using a Active Directory Account is there a way to check if the account can log into SQL Before i give it out. I tried sqlcmd but I couldn't get it to work. Is that for SQL accounts only?
Thanks for any help
Accidental DBA

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 1

n00bDBA (3/31/2012)
Hello,
If I create a SQL Login using a Active Directory Account is there a way to check if the account can log into SQL Before i give it out. I tried sqlcmd but I couldn't get it to work. Is that for SQL accounts only?
Thanks for any help
Accidental DBA

Hi,

Of course there is a way. Pleas see this http://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server

Regards

Igor Micev,My blog: www.igormicev.com

n00bDBA SSCrazy Points: 2986 More actions · Answer 2

Hi,

Im happy (well happy enough) on how to make the account!

Its how to check the account works.

With ODBC i think I would have to log in as the account to check Windows Authentication.

I cant work out how to use .UDL with a Windows Auth i tried "account@domain.local"

I cant get SQLCMD to use a different Windows auth

Im hoping that its just me doing it wrong!

Thanks

Your Name Here SSCrazy Points: 2344 More actions · Answer 3

Click the Start button.

Navigate to Programs/Accessories

Right-click on Command Prompt

Click "Run as..."

Click "The following user:" radio button

Type the AD user name and password and click the OK button

In the Command Prompt window, at the prompt, type SQLCMD /S yourserver /E and press enter

At the 1> prompt, type SELECT SUSER_SNAME() and press enter

At the 2> prompt, type GO and press enter

Your query will return the name of the user that you're logged in as

At the 1> prompt type QUIT and press enter.

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 4

This works in SSMS

-- Change execution context to the desired login

EXECUTE AS LOGIN = 'Domain\LoginName';

GO

--Check the execution context

SELECT SUSER_NAME();

GO

-- Do stuff

--Revert to me

REVERT;

GO

Your Name Here SSCrazy Points: 2344 More actions · Answer 5

<LOL>

Thanks, Cath. That's what came to mind for me as well when I first read his post but it seemed he had his heart set on using SQLCMD. Maybe I read too much into it but I haven't had my coffee yet so everything is sorta blurry... <g>

n00bDBA SSCrazy Points: 2986 More actions · Answer 6

haha!

your both geniuses!

Thanks for opening my eyes!