Hi Team,
I am trying to access SQL Database tables where the IP address/Hostname, User ID and password information are stored.
This is how it is setup:
- I have created custom tables in SQL with IP address as navchar datatype, user ID as navchar datatype and password as navchar datatype
- I have inserted data into the columns, added the IP address, user ID as domainname\username and password in plain text
- With the below script, I am unable to connect to SQL Database and query the table to get the output individually
But when I try to assign the values from SQL table to variables and try to connect to the vCenter, I get an error as to the host is not resolvable. But I can use same information and connect with a different session.
I am using the below script, where Get-MOLDatabaseData function connects to SQL Server.
Can someone please look into the script and let me know if there is anything missing?
# load the VMware module
$VimAutoCore = "VMware.VimAutomation.Core"
if ( (Get-PSSnapin -Name $VimAutoCore -ErrorAction SilentlyContinue) -eq $null ) {
Write-Host "loading $VimAutoCore powershell module"
Add-PsSnapin $VimAutoCore
}
function Get-MOLDatabaseData {
[CmdletBinding()]
param (
[string]$connectionString,
[string]$query,
[switch]$isSQLServer
)
if ($isSQLServer) {
Write-Verbose 'in SQL Server mode'
$connection = New-Object -TypeName `
System.Data.SqlClient.SqlConnection
} else {
Write-Verbose 'in OleDB mode'
$connection = New-Object -TypeName `
System.Data.OleDb.OleDbConnection
}
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $query
if ($isSQLServer) {
$adapter = New-Object -TypeName `
System.Data.SqlClient.SqlDataAdapter $command
} else {
$adapter = New-Object -TypeName `
System.Data.OleDb.OleDbDataAdapter $command
}
$dataset = New-Object -TypeName System.Data.DataSet
$adapter.Fill($dataset) | Out-Null
$dataset.Tables[0]
$connection.close()
}
#This variable connects to SQL Server Database
$SQLConnectionString = "Server=hostname;Database=dbname;User Id=sa;Password=password;"
#Variable to retrieve vCenter IP, Username and password
$vCenterIP = Get-MOLDatabaseData -connectionString $SQLConnectionString -isSQLServer -query "SELECT vCenterIP FROM server_table"
$vCenterUser = Get-MOLDatabaseData -connectionString $SQLConnectionString -isSQLServer -query "SELECT UserID FROM server_table"
$vCenterPassword = Get-MOLDatabaseData -connectionString $SQLConnectionString -isSQLServer -query "SELECT Password FROM server_table"
#Connect to vCenter Server
Connect-VIServer -Server $vCenterIP -User $vCenterUser -Password $vCenterPassword -ErrorAction Stop
I'm afraid I don't understand what the problem actually is, does the hostname in the connectionstring not resolve, or are you not able to connect to the vCenter ?
Perhaps you could include the actual message you are getting ?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Hi Luc,
Here's the output:
Connect-VIServer : 21/8/2013 9:28:09 AM Connect-VIServer Could not
resolve the requested VC server.
At C:\Citi_Scripts\Snapsots_30days.ps1:20 char:1
+ Connect-VIServer -Server $vCenterIP -User $vCenterUser -Password
$vCenterPasswor ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~
+ CategoryInfo : ObjectNotFound: (:) [Connect-VIServer], ViServer
ConnectionException
+ FullyQualifiedErrorId : Client20_ConnectivityServiceImpl_Reconnect_NameR
esolutionFailure,VMware.VimAutomation.ViCore.Cmdlets.Commands.ConnectVISer
ver
Although I am able to use the same IP and credentials and connect manually.
One thing you can dp is check if the following lines return the actual value you need to do a Connect-Viserver
$vCenterIP = Get-MOLDatabaseData -connectionString $SQLConnectionString -isSQLServer -query "SELECT vCenterIP FROM server_table"
$vCenterUser = Get-MOLDatabaseData -connectionString $SQLConnectionString -isSQLServer -query "SELECT UserID FROM server_table"
$vCenterPassword = Get-MOLDatabaseData -connectionString $SQLConnectionString -isSQLServer -query "SELECT Password FROM server_table"
Maybe do a write-host to see the variable contents in each line right after and before doing a Connet-Viserver?
Hi,
Thanks for your reply, the write-host command gives me the below output:
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
I guess write-output should be the correct command, I am able to see the IP, user ID and Password when I run write-output of $vCenterIP, $vCenterUser and $vCenterPassword variables.
I think thats the problem.
The data that is being passed on the Connect-Viserver is not the correct one.
What you can do is:
Connect-VIServer -Server $vCenterIP.vCenterIP
or
$realvCenterIP = (Write-outPut $vCenterIP).vCenterIP
the above method only picks up the vCenterIP and not other values for Username and password variable.
You try it without username and password parameter and see if it prompts you for one. if it does, it means it got the correct value from the DB for the vCenter part.
You then need to do the same for Username and Password.
$realvCenterIP = (Write-outPut $vCenterIP).vCenterIP
$realvCenterUser = (Write-outPut $vCenterUser).vCenterUser
$realvCenterPassword = (Write-outPut $vCenterPassword).vCenterPassword
Use that to do the Connect-Viserver
Connect-VIServer -Server $realvCenterIP -User $realvCenterUser -Password $realvCenterPassword
I tried it.. (Write-outPut $vCenterUser).vCenterUser gives me a blank output
Basically what's happening here is when I retrieve the values from SQL table...they do get assigned to the variable but the write-host command displays the TypeName:System.Data.DataRow