0

I have an Oracle database connected to a SQL Server. The connection works correctly, but unfortunately, I don't know the password used for that connection. Now I need to change the password in Oracle.

Is it a good idea to run something like

SELECT * FROM OPENQUERY([oracle], '
     ALTER USER OracleUser IDENTIFIED BY pswd;
     SELECT 1 FROM DUAl')

Or maybe, is there another solution?

UPD My query ends with error

The OLE DB provider "OraOLEDB.Oracle" for linked server "oracle" indicates that either the object has no columns or the current user does not have permissions on that object

Dzmitry Paliakou
  • 1,587
  • 19
  • 27
  • https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d9580b8-c335-4344-8691-32d56a39b700/oracle-user-password-change-via-linked-server-with-sql-server-2005?forum=sqldatabaseengine - maybe this will work, never tried it. – Robert Dupuy Sep 25 '15 at 12:23
  • surely a better idea would be to log onto the Oracle database with a user who has the privilege to change passwords, DBA role as an example, and work from there? – kevinskio Sep 25 '15 at 12:54
  • 1
    Thank you Robert. EXEC('...') AT [oracle] works for me. This query caused error `Server ‘oracle′ is not configured for RPC for a Linked Server`, but I altered linked server according to [this article](http://www.thesqlreport.com/?p=120). And now I'm stumped with an oracle error `ORA-28221: REPLACE not specified`. It seems I need to call the oracle administrator. – Dzmitry Paliakou Sep 25 '15 at 13:05
  • You need to specify the old password. (Unless this is the first time you set the password) – Ben Sep 25 '15 at 15:05

1 Answers1

1

Correct syntax for changing password in Oracle from SQL Server via linked server is 

EXEC ('ALTER USER OracleUser IDENTIFIED BY new_password REPLACE old_password') AT [oracle]
Dzmitry Paliakou
  • 1,587
  • 19
  • 27