0

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

3
  • Why do you expect that Oracle's tnsnames.ora syntax would work with SQL Server? Take a look at connection strings for SQL Server. Commented May 27, 2020 at 8:00
  • Changed the question title @vonPryz Need to connect Oracle db not a SQL server Commented May 27, 2020 at 8:08
  • Why do you expect that .NET Data Provider for SQL Server would connect to an Oracle database? Anyway, try this answer. Commented May 27, 2020 at 9:00

1 Answer 1

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 
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.