Search

Top 60 Oracle Blogs

Recent comments

SQL Server on Oracle Cloud

By Franck Pachot

.
You can create a VM with SQL Server running in the Oracle Cloud. This is easy with a few clicks on the marketplace:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 1536w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 1733w" sizes="(max-width: 1024px) 100vw, 1024px" />
Here are the steps I did above:

  • Oracle Cloud -> Marketplace -> Application -> Category -> Database management
  • You have multiple flavors. I’ve chosen the lastest cheaper “Microsoft SQL 2016 Standard with Windows Server 2016 Standard”
  • Select the compartment and “Launch Instance”
  • Choose a name, and the network

This is fast but don’t be as impatient as I am, the password is displayed after a while:

After a few minutes, you have the user (opc) and password (mine was ‘BNG8lsxsD6jrD’) that you will have to change at the first connection. This user will allow you to connect with Remote Desktop. This means that you have to open the 3389 TCP port:

  • You find your instance in Oracle Cloud -> compute -> instances (don’t forget that this is just easy provisioning for a database running on IaaS. It is not a PaaS managed service
  • Subnet -> Security List -> Add Ingress Rules -> IP Protocol TCP, Destination port: 3389

Once this port is opened in the ingress rules, you can connect with Remote Desktop and access the machine as the user OPC which is a local administrator. You can install anything there.

There’s something that quickly annoys me when I want to install something there – Internet Explorer and its “enhanced security configuration”. I disable this and take my responsibility for what I want to download there:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 1536w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 1770w" sizes="(max-width: 1024px) 100vw, 1024px" />

SSMS – the SQL Server Management Studio is not installed on the server. You have the command line with “sqlcmd” and the SQL Server Configuration manager where you can verify that the TCP access to the database is enabled and on the default port 1433:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 1536w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 1693w" sizes="(max-width: 1024px) 100vw, 1024px" />

I’ve the SSMS installed on my laptop and as I’ve created this VM on the public subnet, I open the ingress TCP port 1433:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 1536w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 1923w" sizes="(max-width: 1024px) 100vw, 1024px" />
But that’s not sufficient because the installation from the marketplace does not open this port. You need to open it in the windows firewall:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 1536w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 1000w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 670w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 1776w" sizes="(max-width: 1024px) 100vw, 1024px" />
And… one more thing… the installation from the marketplace allows only Windows Authentication to the database but, if you don’t share a domain, you can’t connect remotely with this.

I’ve created a user:


Microsoft Windows [Version 10.0.14393]
(c) 2016 Microsoft Corporation. All rights reserved.

C:\Windows\system32>sqlcmd

create login franck with password='Test-dbi-2020';
alter server role sysadmin add member franck;
go

Then, in order to connect with a SQL Server user I had to enable SQL Server authentication:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/08/Annotat... 1276w" sizes="(max-width: 1024px) 100vw, 1024px" />

You may wonder how I enabled it without having SSM first? I’ve read https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode?view=sql-server-ver15 that mentions:


EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'LoginMode', REG_DWORD, 2
GO

to run on SQLCMD.
But I thought I was more clever, ran regedit.exe and changed HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer to add a ‘LoginMode’ key. But it didn’t work. I finally realized that the registry key is: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer.
This is a very subtle difference, isn’t it? Ok, now I admit it: I installed SSMS on the cloud VM before finding this </p />
</p></div>

    	  	<div class=