Connecting to Oracle database using powershell script. Error while connecting

1

I am trying to connect to a database using the Hostname and SID values through a powershell script.

My code:

$DataSource ='HOSTNAME:PORT/INSTANCE'
    $Username='Username'
    $Password='Password'

    #List
    $UserDataDT = New-Object System.Data.DataTable

    $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
    $SQLConnection.ConnectionString = "Data Source=$DataSource;User Id=$Username;Password=$Password;"   
    $SQLCmd= New-Object System.Data.SqlClient.SqlCommand
    $SQLConnection.open();
    $SQLCmd.Connection = $SQLConnection
    $SqlCmd.CommandTimeout = 600

I am getting the following error:

catch Exception...
System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establish
ing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is corre
ct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could n
ot open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): The filename, directory na
me, or volume label syntax is incorrect
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString conn
ectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Bool
ean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionP
ool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderMan
ager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poo
lKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOpti
ons)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningOb
ject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOption
s, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userO
ptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObje
ctsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal
& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 ret
ry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSourc
e`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnecti
onFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at CallSite.Target(Closure , CallSite , Object )
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:123,State:0,Class:20

I've also tried keeping the $DataSource value as:

$DataSource ='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=hostname)(Port=0000)))(CONNECT_DATA=(SID=abc)))'

Could anyone help me what is the issue with my scirpt.

Thanks in advance

sql
powershell
asked on Stack Overflow May 27, 2020 by Jagbir Singh • edited May 27, 2020 by Jagbir Singh

1 Answer

0

Connected using the Oracle database. Made changes accordingly and it worked, Used Oracleclient entry instead of SQLClient.

 $OracleConnection = New-Object System.Data.OracleClient.OracleConnection 
answered on Stack Overflow May 27, 2020 by Jagbir Singh

User contributions licensed under CC BY-SA 3.0