0

I want to connect an Azure Database from Oracle SQL Developer. I entered the user credentials and server details in the below format.

  • user name : myusername@myserver.net
  • host : myserver.net;ssl=request

But when I tried to connect, it throws error as

Status : Failure -Test failed: Login failed for 'myusername'

I can connect to the same using SQL Server Management Studio with below settings.

  • Authentication : Azure Active Directory - Universal with MFA
  • Network Protocol : Default
  • Encrypt Connection : Ticked

What should I do to sort out this?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Happiness
  • 21
  • 5
  • 1
    What do you mean `Azure Database`? You can't use an Oracle tool to connect to SQL Server (that includes Azure SQL) if that's what you ask. SQL Server and Oracle are completely different products. Even if Oracle SQL Developer can somehow connect to SQL Server, there's nothing you can do if it doesn't support Azure AD – Panagiotis Kanavos Dec 08 '22 at 09:14
  • 1
    Why are you trying to use Oracle SQL Developer when you already use SSMS? What's the actual problem you try to solve by using an Oracle tool? There's certainly a better solution – Panagiotis Kanavos Dec 08 '22 at 09:16
  • Oracle Sql developer definitely can connect to Microsoft Sql server. Since this db is in the azure cloud and the authentication is through active directory, it may require some additional steps to connect. That's what I am trying to do. – Happiness Dec 08 '22 at 12:55
  • I prefer sql developer over ssms as it have some functionalities that I like, but doesn't exist in ssms. – Happiness Dec 08 '22 at 12:57
  • You just found out that Oracle Developer is lacking functionalities that you like, that exists in SSMS, Azure Data Studio, Jetbrain products and a lot of other products. Perhaps even Jupyter and VS Code notebooks. You haven't explained what you want yet. – Panagiotis Kanavos Dec 08 '22 at 13:01
  • No, its the other way. For instance, sql developer allows you to export the result set of any select statement as insert scripts just by right clicking on the selected result set and choose to export as . sql. But in ssms, we can't do that. Also, I am more familiar with SQL developer than ssms. – Happiness Dec 08 '22 at 13:06
  • What are you trying to do? Whatever it is, it's probably already available, just not where you're looking for it. Database projects and schema comparison are available in Azure Data Studio, Visual Studio, JetBrains DataGrip. A tool created for Oracle won't work well with other products even if it can connect to them. It's not a general purpose database administration or development tool. – Panagiotis Kanavos Dec 08 '22 at 13:06
  • `to export the result of any select statement data as insert scripts just by right clicking on the selected result set and choose to export as . sql.` you realize how *in*efficient that is? Generating 100 INSERTs is a lot slower than 1 INSERT with 100 row values, which in turn isn't as fast as a `BULK INSERT` or `bcp`. For entire databases, real backups are a lot faster than scripting the database. – Panagiotis Kanavos Dec 08 '22 at 13:10
  • Besides, I'm sure someone created an ADS extension for this. – Panagiotis Kanavos Dec 08 '22 at 13:10
  • Do you have any documentation that says oracle sql developer cant connect to sql server db? Oracle documentation says it can connect to sql server and several other non oracle databases. – Happiness Dec 08 '22 at 13:10
  • I am not looking for entire back up. I have to take insert scripts for around 20-100 select statements i write everyday, i don't care about the performance as i use those scripts as a back up only. Why you are stuck on the functionality differences between ssms and sql developer. Suppose one user is more comfortable with sql developer than ssms, also sql developer allows connection to sql sever. So please try to focus on the requirement to establish the connection to sql server from sql developer instead of focusing on comparing ssms with sql developer when that's not the question asked. – Happiness Dec 08 '22 at 13:16
  • Because you just asked a product question that's out of scope for SO - either Oracle Developer supports Azure SQL with Azure AD login or not. It's not a matter of setting an IP. That's a completely different authentication mechanism. That's a question for Oracle's support forums, not SO. We can help you do what you actually want with tools that actually work with Azure SQL – Panagiotis Kanavos Dec 08 '22 at 13:18
  • For example [Converting Select results into Insert script - SQL Server](https://stackoverflow.com/questions/4526461/converting-select-results-into-insert-script-sql-server) has answers that show how to convert query results to INSERT clauses. The answers aren't very pretty but work in both SSMS and ADS. – Panagiotis Kanavos Dec 08 '22 at 13:22
  • The article [Generate data scripts using SSMS and Azure Data Studio](https://www.sqlshack.com/generate-data-scripts-using-ssms-and-azure-data-studio/) shows how to generate data scripts and mentions an ADS extension that does what you want. And since ADS is cross platform, the extension is too – Panagiotis Kanavos Dec 08 '22 at 13:24
  • I didn't clearly understand what you reffered to as product question. If i have posted this in a wrong section in SO, can you please let me know which section in SO(not in Oracle support forum) I should post this? – Happiness Dec 08 '22 at 13:30
  • Thanks for providing links to other related threads, but I had already gone through them before posting the question. One is just explaining how we can export entre data in any table, is not about how to export the selected result of a select query. Other one serves the purpose but it is a very lengthy procedure compared to straight forward export functionality available in sql developer – Happiness Dec 08 '22 at 13:35
  • 1
    You're asking whether Oracle Developer supports Azure AD login at all. If it did, you'd see it as an option along with Windows Authentication and SQL Server login. If it's not there, it's not there. *Maybe* you can work around this if you use the [SQL Server ODBC driver](https://learn.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver16) and configure a data source to use multi-factor authentication. – Panagiotis Kanavos Dec 08 '22 at 13:39
  • that is what I am looking for. Can you give some more detail/steps on how to do that ? – Happiness Dec 08 '22 at 13:42
  • Did you read the docs? The link explains what to install, has step-by-step instructions and screenshots for each authentication type, and shows what MFA login will look like – Panagiotis Kanavos Dec 08 '22 at 13:43
  • Hi, the db sever is already configured to use MFA. But what I want to know how to use Oracle developer to connect to this db server? More preciously, what options should I select and in which screens during login – Happiness Dec 18 '22 at 13:39

0 Answers0