New-PSDrive with SQL Authentication internally is using Integrated Authentication - by Arvind Shyamsundar

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


7
0
Sign in
to vote
ID 473953 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 7/12/2009 8:26:05 AM
Access Restriction Public

Description

While trying to connect to a remote instance of SQL Server (which is configured to use mixed mode authentication) if we register using New-PSDrive and specify the right SQL Server authentication credentials (as documented in Books Online), it fails to connect. If you capture a SQL Server Profiler trace on the remote server, you notice that the PowerShell client is internally connecting twice: once with Windows credentials and if that succeeds only does it connect using the SQL Server credentials we specified.

Sign in to post a comment.
Posted by Cody Konior on 7/15/2014 at 12:21 AM
Why does the SQLProvider need to use WMI? And/or why can't we provide our own WMI credentials also?
Posted by Alexander Kuzin on 6/11/2014 at 3:41 AM
Isn't it fixed yet?
I try to connect to SQL Server 2012 with SQL Server user name and password. Invoke-Sqlcmd with specified -Username and -Password works fine. But New-PSDrive with specified credentials fails with error:
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'BGD' failed with the following error: Access denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'BGD' failed with the following error: Access denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'BGD' failed with the following error: Access denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'BGD' failed with the following error: Access denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'BGD' failed with the following error: Access denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'BGD' failed with the following error: Access denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
New-PSDrive : SQL Server PowerShell provider error: Path SQLSERVER:\SQL\BGD does not exist. Please specify a valid path.
строка:26 знак:1
+ New-PSDrive 'DELTA' -PSProvider SqlServer -Root "SQLSERVER:\SQL\BGD" -Creden ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo         : InvalidArgument: (SQLSERVER:\SQL\BGD:String) [New-PSDrive], GenericProviderException
    + FullyQualifiedErrorId : PathDoesNotExist,Microsoft.PowerShell.Commands.NewPSDriveCommand
Posted by Microsoft on 1/3/2011 at 1:09 AM
Hi

Greetings from the SQL Server Manageability team.

Thanks for writing in to Microsoft. We greatly value your feedback.

On remote machine we have two security layer access. First we have to cross through WMI and then we have to pass the SQL Server security. Given login and password is valid to access the SQL Server on remote machine, not access to pass through WMI.

Error message:

"An attempt to connect to WMI on <remote-machine-name> failed with following error: Access is denied: (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"


Scenario:

I have a Windows account, say “User1”, on machines M1 & M2 but that account “User1” doesn’t have access to SQLServer on M2.

Then if try to do the following from M1, it works fine.

$pwd = read-host -AsSecureString -Prompt "Password"
$login = "sa"
$cred = new-object System.Management.Automation.PSCredential -argumentlist $login,$pwd
New-PSDrive "newmachine" -PSProvider SqlServer -Root "SQLSERVER:\SQL\M2\DEFAULT" -Credential $cred -Scope 0

This means that we are able to connect to the SQLServer on M2 but since the Windows login “User1” doesn’t have access to SQLServer on M2, it is indeed connecting using “sa” credentials.

Thanks again for providing feedback and making SQL Server the greatest Database server.


Regards,
Sandeep Ch. v
Posted by Microsoft on 7/31/2009 at 9:31 AM
Thanks for your feedback. This is a bug and we will fix it in the next major releases.

Mingwu