April 29, 2015 at 3:00 am
I am trying to user PowerShell to connect to a SQL server as follows:
Server A
- PowerShell script runs here.
- Uses Microsoft.SqlServer.Smo namespace to make the connection to Server B
- Using ConnectAsUsername with a active directory FID.
Server B
- SQL server
- Active directory FID permissioned correctly to database.
My problem is that on Server A, the FID needs to have the "Allow Log on locally" User Right Assignment for the connection attempt to Server B to work. I find this strange since I am not actually connecting to Server A with the FID. The error message received is the usual ""Logon failure: the user has not been granted the requested logon type at this computer.". My testing has shown that the FID does not need any URA assignments on Server B.
Is there a way to avoid granting the "Allow Log on locally" User Right Assignment? Perhaps a different approach?
This is the code running on Server A:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = "xxxxxxxx,1433"
$serverConnection = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlServer)
$SQLQuesryResults = ""
$SQLData = ""
$serverConnection.ConnectAsUser = $true
#$serverConnection.ConnectAsUsername = "user123@adomain.net"
$serverConnection.ConnectAsUserPassword = "MyPassword"
$serverConnection.Connect()
April 29, 2015 at 5:22 am
Should the PowerShell be running on Server A? Could you open a remote PowerShell session of Server B and run things on that Server?
Get-Help New-PSSession
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
April 29, 2015 at 6:29 am
We do have a need to run this from a specific server which is not the SQL server. I can't for the life of me find out why the FID needs allow log on locally on Server A.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply