Connect or Mount USB Storage drives in Windows Linux Sub System

Hi folks,

This post will explain the process of connecting / mounting USB devices to Windows Linux Sub Systems

Mount removable media (e.g. E:)

$ sudo mkdir /mnt/e
$ sudo mount -t drvfs E: /mnt/e

Unmount

$ sudo umount /mnt/e

Send your comments and feedback to psrdotcom@gmail.com

Microsoft Azure SQL Server Read Only User Creation Deletion

Hi folks,

Today I will explain, how you can easily manage the readonly (view) users in Azure Microsoft SQL Server

Procedure

In Azure SQL Server

CREATE LOGIN [testuser] WITH PASSWORD = 'random_p@$$w0rd';

For master and each DB

Note: You must create the user in master db before creating in other databases

CREATE USER [testuser] FOR LOGIN [testuser]   
    WITH DEFAULT_SCHEMA = [dbo];  
GO

Grant Connect permission

GRANT CONNECT TO [testuser]
GO

Give datareader role to read (view) only

ALTER ROLE db_datareader ADD MEMBER [testuser]
GO

Drop user in DB

DROP USER [testuser]

GO

Drop user in Azure SQL

DROP LOGIN [testuser];
GO

Please send your comments and feedback to psrdotcom@gmail.com

Microsoft SQL Server 2019 Express Docker image Example

Hi Folks,

Today I am going to explain the procedure for connecting to a Microsoft SQL Server 2019 Express edition docker image

Pre-requisites

1. Docker Desktop
2. Windows OS
3. Powershell/command prompt

Procedure

Get and run docker image

> docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<Your_Password>" -e "MSSQL_PID=Express" --name "<Your_SQL_Server_Name>" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Command information

Remove the "MSSQL_PID=Express" to run other version of SQL Server

Replace 2019 with required SQL Server version

Password should be atleast 8 characters with capital, small, numeric, special character combination

Use different port if you already have a local sql server

Name should not contain spaces

Check for docker container

> docker ps

You should able to see the container with your SQL Server name at the end in running status

Connect to SQL Server

docker exec -it "<Your_SQL_Server_Name>" /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<Your_Password>"

You should be able to see "1>" prompt

Command information

Use the SQL Server name or user the container ID

Use database and play around with table(s)

Important

Multiple commands can be entered one after one, but to execute the set of command(s), you need give "GO" command.

Create Database

CREATE DATABASE SampleDB

GO

List all databases

SELECT Name from sys.Databases

GO

Start using the database

USE SampleDB

GO

Create table

CREATE TABLE UserInfo ( Id INT, Name VARCHAR(64))

GO

Insert values

INSERT INTO UserInfo (1, 'ABC')

GO

Retrieve table contents

SELECT * FROM UserInfo

GO

Exit from SQL Server

QUIT

Hope you are able to run the SQL Server Docker image.

Please send your feedback and comments to psrdotcom@gmail.com

Connect to remote Oracle database server from RedHat Linux 7 using Oracle InstaClient

Hi friends,

I have come across a situation, where I need to connect to remote Oracle database server from RedHat Enterprise Linux (RHEL) 7. I found a way of using the Oracle InstaClient instead of installing the full Oracle DB which internally consists of Client.

Pre-requesites

1. Linux mahcine (I tried with RHEL 7) with following softwares
• Java (JAVA_HOME should be available)
• Development Tools (RHEL 7 tools like gcc etc.)
2. Another machine where Oracle server is installed with the following config
• Database schema should be available
• Users should be available instead of system/sysdba users
3. Both machines shall be reachable to each other via ping.

Note: You should know the database server SID/service name.

Procedure

Download

1. Download the insta client from Oracle Official Page
2. In my case, I have downloaded the "Instant Client for Linux x86-64"
3. Accept the license agreement
4. Click "oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64.rpm" to download
5. Optionally, you can download the "oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64.rpm" to have sqlplus command line utility for testing
6. Alternatively, you can download the zip files and download the same to install.
7. In my case, I am going with RPMs.

Install

• Install the downloaded RPMs
• #> yum install oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64.rpm
• #> yum install oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64.rpm
• While installing, it asks for locations, accept the defaults by pressing the "Enter" button

Note: If any dependencies are there, please install those.

Environment Variables

• Create a oracle_env.sh file and setup your variables
• #> gedit /etc/profile.d/oracle_env.sh

export ORACLE_HOME=/usr/lib/oracle//client/
export TNS_ADMIN=/usr/lib/oracle//client/
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

• Save your file
• Source your file with following command
• #> source /etc/profile.d/oracle_env.sh

tnsnames.ora

• Create tnsnames.ora file in your $ORACLE_HOME with following syntax

localsid = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
    (CONNECT_DATA =
      (SID = )
    )
  )

• Instead of SID, you can use SERVICE_NAME also.

Connect

• You can test the connection
• #> sqlplus @

You should be able to connect to your remote database.

Hope, it is useful for you.

Please send your comments and feedback to psrdotcom@gmail.com