New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Test-DbaIdentityUsage attempts using current Windows account before specified -SqlCredential #2012

Closed
Bendy22 opened this Issue Aug 4, 2017 · 4 comments

Comments

Projects
None yet
4 participants
@Bendy22

Bendy22 commented Aug 4, 2017

Bug Report

$cred = Get-Credential
Test-DbaIdentityUsage -SqlInstance MyInstance -SqlCredential $cred

I noticed that, when running PowerShell as a Windows user without login privileges to the destination SQL instance that I receive a login failure in the SQL errorlog even when specifying the -SqlCredential parameter. What is happening is that the command is trying the active Windows user running PowerShell first before trying what is specified via the -SqlCredential parameter. This would likely go unnoticed if the Windows account running PowerShell has permissions to run the cmdlet on the specified -SqlInstance. I noticed this due to the Windows account I was using not having permissions to the SQL instance and automatic email notifications set up to alert whenever a failed login gets logged to the SQL errorlog. This has been confirmed with Profiler traces.

I have tested Connect-SqlInstance as suggested by @potatoqualitee and @wsmelton, but it does not suffer from the same problem.

#This works - does not first attempt to login with Windows account
$cred = Get-Credential
Connect-SqlInstance -SqlInstance MyInstance -SqlCredential $cred

Destination servers running Windows Server 2008 R2 and SQL Server 2008 R2 show this in the errorlog when ran from the context of a Windows user without SQL login permissions:
image

Destination servers running Windows Server 2012 R2 and SQL Server 2014 show this in the errorlog when ran from the context of a Windows user without SQL login permissions:
image

#This also works - does not first attempt to login with Windows account
$cred = Get-Credential
$srv = Connect-DbaSqlServer -Credential $cred -SqlInstance MyInstance
Test-DbaIdentityUsage -SqlInstance $srv

Connecting to the instance using Connect-DbaSqlServer first and then passing that into Test-DbaIdentityUsage works without the errorlog login failure when ran from the context of a user without SQL instance login permissions.

Here is a SQL Profiler trace screenshot showing the process when executed from the context of a user that does not have SQL login permissions:
image

General Troubleshooting steps

see above

Version Information

Source Operating System: Windows Server 2012 R2

dbatools version:

PS C:\Windows\system32> (Get-Module dbatools).version

Major  Minor  Build  Revision
-----  -----  -----  --------
0      9      22     -1      

Local SQL Server version: 2014
Remote SQL Server version: 2014

Steps to Reproduce

$cred = Get-Credential
Test-DbaIdentityUsage -SqlInstance MyInstance -SqlCredential $cred

Also, running just Test-Dba-IdentityUsage -SqlInstance MyInstance gives the login failure directly to the screen when executed from the context of a user without SQL login permissions:

PS C:\Windows\system32> Test-DbaIdentityUsage -SqlInstance MyInstance
WARNING: [Test-DbaIdentityUsage][12:44:20] Failure | Can't connect to MyInstance: System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'DOMAIN\username'.

Problem to solve

Don't have theTest-DbaIdentityUsage cmdlet use the current PowerShell session's Windows credential if the -SqlCredential parameter is set.

@potatoqualitee

This comment has been minimized.

Member

potatoqualitee commented Aug 4, 2017

hey @FriedrichWeinmann - something so weird - Get-DbaDatabase works for him with no issue. I'm looking a the code and just can't track it down. I feel like this just needs some debug love and my brain's not in it right now

@wsmelton wsmelton self-assigned this Aug 4, 2017

@wsmelton

This comment has been minimized.

Member

wsmelton commented Aug 4, 2017

I'm setting up the lab with a test so I can run commands under a AD account that does not have rights to any of the instances.

@wsmelton

This comment has been minimized.

Member

wsmelton commented Aug 5, 2017

Ok @Bendy22 I've got this figured out...but good grief this is going to take a bit of typing to explain 🎨

@potatoqualitee and @FriedrichWeinmann this will require changing how the $server object is built so I'm not sure yall are going to like the way I figured out how to do it...but the essay to follow will explain why. [I'll do it in the morning.]

@Bendy22

This comment has been minimized.

Bendy22 commented Aug 16, 2017

Thanks for fixing this! You guys rock!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment