Tuesday, December 3, 2019

Router - Enable OpenVPN

OpenVPN Enabling:

Asus RT-N66U is used in this case to enable the VPN server service on the router.  First enable the OpenVPN


This router is behind a cable modem so the correct host IP was not able to be obtained correctly.  You can manually modify the exported .ovpn file later.

If you preferred to play with the advanced please select it from "VPN details" option.  Below is what I used:


I did enable the channel encryption so it can void the hijack by DPI (Deep Packet Inspection).  In my case I also have to enable the port forwarding from my cable modem to my router.

Once the configuration is set then you should export it to a .ovpn file and send to the client who will need to connect to this OpenVPN service.  I recommend to download OpenVPN client for all platforms then import the configuration file.

This VPN service does take a lot of CPU cycle from the router so the bandwidth (or speed) drop quite a lot.  I experienced a speed drop from 80 Mbps to 8 Mbps.


SSH enabling:

If you like to enable SSH server service on this router you can also download the final Merlin firmware from:

https://sourceforge.net/projects/asuswrt-merlin/files/RT-N66U/Release/

Build "RT-N66U_380.70_0.zip" is the final build and support to RT-N66U has been stoped since April-2018.

Thursday, July 4, 2019

Windows - Hide Unwanted SSID

Run Cmd in Administrator mode:
netsh wlan add filter permission=block ssid="WIFI NAME" networktype=infrastructure

Wednesday, May 4, 2016

Linux - 70 Useful Linux Scripting Techniques

70 Shell Scripting Interview Questions & Answers

May 18, 2015 | By  in SHELL SCRIPTS
We have selected expected 70 shell scripting question and answers for your interview preparation. Its really vital for all system admin to know scripting or atleast the basics which in turn helps to automate many tasks in your work environment. In the past few years we have seen that all linux job specification requires scripting skills.

1) How to pass argument to a script ?

./script argument
Example : Script will show filename
./show.sh file1.txt
cat show.sh
#!/bin/bash
cat $1

2) How to use argument in a script ?

First argument: $1,
Second argument : $2
Example : Script will copy file (arg1) to destination (arg2)
./copy.sh file1.txt /tmp/
cat copy.sh
#!/bin/bash
cp $1 $2

3) How to calculate number of passed arguments ?

$#

4) How to get script name inside a script ?

$0

5) How to check if previous command run successful ?

$?

6) How to get last line from a file ?

tail -1

7) How to get first line from a file ?

head -1

8) How to get 3rd element from each line from a file ?

awk '{print $3}'

9) How to get 2nd element from each line from a file, if first equal FIND

awk '{ if ($1 == "FIND") print $2}'
10) How to debug bash script
Add -xv to #!/bin/bash
Example
#!/bin/bash –xv

11) Give an example how to write function ?

function example {
echo "Hello world!"
}

12) How to add string to string ?

V1="Hello"
V2="World"
let V3=$V1+$V2
echo $V3
Output
Hello+World

13) How to add two integers ?

V1=1
V2=2
V3=$V1+$V2
echo $V3
Output
3
Remember you need to add "let" to line V3=$V1+$V2
then echo $V3 will give 3
if without let , then it will be
echo $V3 will give 1+2

14) How to check if file exist on filesystem ?

if [ -f /var/log/messages ]
then
echo "File exists"
fi

15) Write down syntax for all loops in shell scripting ?

for loop :
for i in $( ls ); do
echo item: $i
done
while loop :
#!/bin/bash
COUNTER=0
while [ $COUNTER -lt 10 ]; do
echo The counter is $COUNTER
let COUNTER=COUNTER+1
done
until loop :
#!/bin/bash
COUNTER=20
until [ $COUNTER -lt 10 ]; do
echo COUNTER $COUNTER
let COUNTER-=1
done

16) What it means by #!/bin/sh or #!/bin/bash at beginning of every script ?

That line tells which shell to use. #!/bin/bash script to execute using /bin/bash. In case of python script there there will be #!/usr/bin/python

17) How to get 10th line from the text file ?

head -10 file|tail -1

18) What is the first symbol in the bash script file

#

19) What would be the output of command: [ -z "" ] && echo 0 || echo 1

0

20) What command "export" do ?

Makes variable public in subshells

21) How to run script in background ?

add "&" to the end of script

22) What "chmod 500 script" do ?

Makes script executable for script owner

23) What ">" do ?

Redirects output stream to file or another stream.

24) What difference between & and &&

& - we using it when want to put script to background
&& - when we wand to execute command/script if first script was finished successfully

25) When we need "if" before [ condition ] ?

When we need to run several commands if condition meets.

26) What would be the output of the command: name=John && echo 'My name is $name'

My name is $name

27) Which is the symbol used for comments in bash shell scripting ?

#

28) What would be the output of command: echo ${new:-variable}

variable

29) What difference between ' and " quotes ?

' - we use it when do not want to evaluate variables to the values
" - all variables will be evaluated and its values will be assigned instead.

30) How to redirect stdout and stderr streams to log.txt file from script inside ?

Add "exec >log.txt 2>&1" as the first command in the script

31) How to get part of string variable with echo command only ?

echo ${variable:x:y}
x - start position
y - length
example:
variable="My name is Petras, and I am developer."
echo ${variable:11:6} # will display Petras

32) How to get home_dir with echo command only if string variable="User:123:321:/home/dir" is given ?

echo ${variable#*:*:*:}
or
echo ${variable##*:}

33) How to get “User” from the string above ?

echo ${variable%:*:*:*}
or
echo ${variable%%:*}

34) How to list users which UID less that 100 (awk) ?

awk -F: '$3<100' /etc/passwd

35) Write the program which counts unique primary groups for users and displays count and group name only

cat /etc/passwd|cut -d: -f4|sort|uniq -c|while read c g
do
{ echo $c; grep :$g: /etc/group|cut -d: -f1;}|xargs -n 2
done

36) How to change standard field separator to ":" in bash shell ?

IFS=":"

37) How to get variable length ?

${#variable}

38) How to print last 5 characters of variable ?

echo ${variable: -5}

39) What difference between ${variable:-10} and ${variable: -10} ?

${variable:-10} - gives 10 if variable was not assigned before
${variable: -10} - gives last 10 symbols of variable

40) How to substitute part of string with echo command only ?

echo ${variable//pattern/replacement}

41) Which command replaces string to uppercase ?

tr '[:lower:]' '[:upper:]'

42) How to count local accounts ?

wc -l /etc/passwd|cut -d" " -f1
or
cat /etc/passwd|wc -l

43) How to count words in a string without wc command ?

set ${string}
echo $#

44) Which one is correct "export $variable" or "export variable" ?

export variable

45) How to list files where second letter is a or b ?

ls -d ?[ab]*

46) How to add integers a to b and assign to c ?

c=$((a+b))
or
c=`expr $a + $b`
or
c=`echo "$a+$b"|bc`

47) How to remove all spaces from the string ?

echo $string|tr -d " "

48) Rewrite the command to print the sentence and converting variable to plural: item="car"; echo "I like $item" ?

item="car"; echo "I like ${item}s"

49) Write the command which will print numbers from 0 to 100 and display every third (0 3 6 9 …) ?

for i in {0..100..3}; do echo $i; done
or
for (( i=0; i<=100; i=i+3 )); do echo "Welcome $i times"; done

50) How to print all arguments provided to the script ?

echo $*
or
echo $@

51) What difference between [ $a == $b ] and [ $a -eq $b ]

[ $a == $b ] - should be used for string comparison
[ $a -eq $b ] - should be used for number tests

52) What difference between = and ==

= - we using to assign value to variable
== - we using for string comparison

53) Write the command to test if $a greater than 12 ?

[ $a -gt 12 ]

54) Write the command to test if $b les or equal 12 ?

[ $b -le 12 ]

55) How to check if string begins with "abc" letters ?

[[ $string == abc* ]]

56) What difference between [[ $string == abc* ]] and [[ $string == "abc*" ]]

[[ $string == abc* ]] - will check if string begins with abc letters
[[ $string == "abc*" ]] - will check if string is equal exactly to abc*

57) How to list usernames which starts with ab or xy ?

egrep "^ab|^xy" /etc/passwd|cut -d: -f1

58) What $! means in bash ?

Most recent background command PID

59) What $? means ?

Most recent foreground exit status.

60) How to print PID of the current shell ?

echo $$

61) How to get number of passed arguments to the script ?

echo $#

62) What difference between $* and $@

$* - gives all passed arguments to the script as a single string
$@ - gives all passed arguments to the script as delimited list. Delimiter $IFS

63) How to define array in bash ?

array=("Hi" "my" "name" "is")

64) How to print the first array element ?

echo ${array[0]}

65) How to print all array elements ?

echo ${array[@]}

66) How to print all array indexes ?

echo ${!array[@]}

67) How to remove array element with id 2 ?

unset array[2]

68) How to add new array element with id 333 ?

array[333]="New_element"

69) How shell script get input values ?

a) via parameters
./script param1 param2
b) via read command
read -p "Destination backup Server : " desthost

70) How can we use "expect" command in a script ?

/usr/bin/expect << EOD
spawn rsync -ar ${line} ${desthost}:${destpath}
expect "*?assword:*"
send "${password}\r"
expect eof
EOD
Good luck !! Please comment below if you have any new query or need answers for your questions. Let us know how well this helped for your interview :-)

This page is a reference to the site:  http://linoxide.com/linux-shell-script/shell-scripting-interview-questions-answers/

Wednesday, January 13, 2016

MSSQL - Double Hopping

Getting the error below when running a SSRS report through SharePoint or a SQL using Linked Server to another server when NTLM security is used instead of SQL account.


SOLUTION:


That error message is almost always related to the "Double Hop" problem. The easiest way to fix it is to make sure all trusted connections are connecting using kerberos instead of NTLM. The alternative would be to use a SQL user instead of trusted auth.

Setting up kerberos is not a simple undertaking, but will resolve this problem and other problems you'll likely experience in the future. I have a many page document specific to our environment (so I can't share all of it) that describes how to set it up. My opening description is as follows:

Configuring SPN's Allows for machines to connect to SQL Server with Kerberos authentication instead of NTLM. Microsoft does not allow NTLM authentication for connections using what is known as the "Double Hop". Microsoft does allow connections using Kerberos to connect using the "Double Hop".

A typical Scenario where you would connect through the "Double Hop" would be by using a linked server. If you connect to SQLSRV1 using Trusted Windows Authentication, and then Select from a linked server that is configured to pass the login's current security context, you have just made a "Double Hop".

If you attempt to do this utilizing NTLM you will get an error saying "Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" This error will occur whether you're trying to connect through IIS first, through SSIS, SSRS, or even while trying to do bulk file operations to a file server as the second hop.


Run the following code on your backend SQL server to determine whether the connections to the server are using kerberos or not.

SELECT    sys.dm_exec_connections.session_id AS SPID, 
  sys.dm_exec_connections.connect_time AS Connect_Time, 
  DB_NAME(dbid) AS DatabaseName, 
  loginame AS LoginName, 
  sys.dm_exec_connections.auth_scheme as Auth_Scheme,
  sys.dm_exec_connections.net_transport AS Net_Transport,
  sys.dm_exec_connections.protocol_type as Protocol_Type,
  sys.dm_exec_connections.client_net_address as Client_Net_Address,
  sys.dm_exec_connections.local_net_address as Local_Net_Address,
  sys.dm_exec_connections.local_tcp_port as Local_TCP_Port
FROM sys.sysprocesses 
Right Outer JOIN sys.dm_exec_connections
ON sys.sysprocesses.spid=sys.dm_exec_connections.session_id
Order By Auth_Scheme, Net_Transport
 

Tuesday, December 8, 2015

MSSQL - Write String to A File


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_TEST_WriteToFile] @FileName VARCHAR(255), @Text1 VARCHAR(MAX)
AS
DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 2, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID

EXECUTE @OLEResult = sp_OADestroy @FS

MSSQL - Some Useful SQL Scripts and Tips

-- get a folder list using xp_cmdshell
CREATE TABLE #Temp(FileName VARCHAR(255))
DECLARE @SQL VARCHAR(1000)
SET @SQL = 'dir "C:\Temp\*.xls" /b'
INSERT INTO #Temp(FileName) EXEC Master..xp_cmdShell @SQL
SELECT * FROM #Temp WHERE FileName IS NOT NULL


-- get a folder list using xp_dirtree
CREATE TABLE #Temp(FileName VARCHAR(255), Depth INT, FileFlag INT)
INSERT INTO #Temp EXEC xp_dirtree 'C:\Temp', 1, 1
SELECT * FROM #Temp



-- find a column name conatins some string
SELECT
     t.name AS table_name
    ,SCHEMA_NAME(schema_id) AS schema_name
    ,c.name AS column_name
FROM sys.tables AS t
JOIN sys.columns c
  ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%ppp%'
ORDER BY schema_name, table_name;



-- check existence of a table
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'NewTableName')
  PRINT 'Not Exists'


-- check existence of a column in a table
IF NOT EXISTS (SELECT * FROM SYS.COLUMNS where Name = N'NewColumnName' AND Object_ID = Object_ID(N'TableName'))
  PRINT 'Not Exists'
begin
    ALTER TABLE BT_CustomerConfiguration
    ADD UTF VARCHAR(10)
End


-- Create store procedure if not exist already
IF OBJECT_ID('dbo.USP_MyStoredProcedure') IS NULL
EXEC('CREATE PROCEDURE dbo.USP_MyStoredProcedure  AS DUMMY:')
ALTER PROCEDURE


-- Cursor Template
DECLARE @tablename VARCHAR(50), SQL VARCHAR(2000)

DECLARE Mycursor CURSOR FOR SELECT Table_name FROM information_schema.tables where TABLE_SCHEMA = 'lp' order by 1
OPEN Mycursor

FETCH NEXT FROM Mycursor  INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
   SELECT @tablename
   FETCH NEXT FROM Mycursor  INTO @tablename
END

CLOSE Mycursor
DEALLOCATE Mycursor


--Creates a DB with default settings if not exist
USE master;
go
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'TestDBAudit')
    DROP DATABASE TestDBAudit;
go
CREATE DATABASE TestDBAudit;
Go


-- drop temp table
IF OBJECT_ID('tempdb..#temp1') IS NOT NULL EXEC('DROP TABLE #temp1')



-- create function which returns a table
ALTER FUNCTION FUNC_LP_GetHistoricCashSummary(@CompanyNum VARCHAR(2), @LeaseNumList VARCHAR(MAX))
RETURNS TABLE
AS

RETURN
(
..
)


-- user account maintenance
SET QUOTED_IDENTIFIER OFF
GO

-- Remove user from each database where the account exists
EXEC sp_Msforeachdb "IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MYDOMAIN\UserName')
                     DROP USER [MYDOMAIN\UserName]"
GO

-- Remove login from this SQL Server
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYDOMAIN\UserName') DROP LOGIN [EPGPDOM\tJanowich] 
GO 


-- Event logging from SQL script
EXEC master..xp_logevent 90000, '###0###', 'ERROR'


-- Check and Delete temp table
IF OBJECT_ID('tempdb..##TEMP_AP') IS NOT NULL EXEC('DROP TABLE ##TEMP_AP')


-- Check and Delete existing table
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '#Temp_Table') EXEC('DROP TABLE #Temp_Table')


-- List all running processes for the default database
DECLARE @DatabaseName VARCHAR(50) = DB_NAME()
SELECT * FROM MASTER.dbo.SysProcesses WHERE DBId = DB_ID(@DatabaseName)


-- List all running processes for the default database
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

KILL [session_id]


-- List all blocked processes
SELECT
     spid,
     status,
     loginame=SUBSTRING(loginame,1,12),
     hostname=SUBSTRING(hostname,1, 12),
     blk = CONVERT(char(3), blocked),
     dbname=SUBSTRING(DB_NAME(dbid),1, 10),
     cmd,
     waittype
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)


-- Check job status
Exec msdb..sp_help_job @job_name = 'Job Name'
--Check for "current_execution_status", if its "4", the job is not running, if "1" its executing.


-- drop all statistics
SELECT 'EXEC (''DROP STATISTICS [' + su.name + '].[' + so.name + '].[' + si.name + ']'')'
FROM sysindexes si
JOIN sysobjects so
ON si.id = so.id
JOIN sysusers su
on su.uid = so.uid
WHERE INDEXPROPERTY(si.id,si.name, 'IsStatistics') = 1
--AND si.name not like '_WA_Sys%'
AND OBJECTPROPERTY(so.id, 'IsUserTable') = 1
ORDER BY so.name, si.name



-- Read and sort Columns
DECLARE @TBLName nvarchar(128)
SET @TBLName = N'MyTable'

SELECT name, object_id FROM sys.tables WHERE name = @TBLName

SELECT name, column_id, object_id
FROM sys.columns
WHERE object_id IN
     (SELECT object_id
     FROM sys.tables
     WHERE name = @TBLName)
order by name


-- using VALUES to construct a temp table
SELECT col1, col2
FROM (VALUES ('January', 1),('Feburary', 2),('March', 3),('April', 4),('May', 5)) AS tbl(col1, col2)
WHERE tbl.col1 = 'January'


-- SQL to truncate all tables
Select DISTINCT 'TRUNCATE TABLE [' + TABLE_NAME + ']'
From INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME like 'LPlus%'


-- Chaneg NVARCHAR to VARCHAR for all tables
Select 'Alter Table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] Alter Column [' + COLUMN_NAME + '] VarChar(' + CAST(CHARACTER_MAXIMUM_LENGTH As VARCHAR) + ')'
From INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'NVARCHAR'


-- Display all running SQL jobs
SELECT sj.name
   , sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
   AND sja.stop_execution_date IS NULL


-- Checking completion of DB recovery
SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests
order by 1 desc


-- Loopback SQL Call
if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB',
    @datasrc = @@servername
go

select * into #t from openquery(loopback, 'exec yourSproc')
select * from #t
drop table #t
go


-- update SQL Statistics
sp_MSForEachDB @command1 = "use ?;exec sp_updatestats"


-- Create Views that mirror another databases tables
SELECT
'create view '+tbl.name+' as select * from plus.dbo.['+tbl.name++']'+char(10)+'go'+char(10)
FROM
sys.tables AS tbl
WHERE
(CAST(
 case
    when tbl.is_ms_shipped = 1 then 1
    when (
        select
            major_id
        from
            sys.extended_properties
        where
            major_id = tbl.object_id and
            minor_id = 0 and
            class = 1 and
            name = N'microsoft_database_tools_support')
        is not null then 1
    else 0
end         
             AS bit)=0)
ORDER BY [Name] ASC