Open Firewall Ports with Powershell

  
  
 
netsh advfirewall firewall add rule name="Open Port 80" dir=in action=allow protocol=TCP localport=80

netsh advfirewall firewall add rule name="Open Port 443" dir=in action=allow protocol=TCP localport=443


netsh advfirewall firewall add rule name="Open Port 1433" dir=in action=allow protocol=TCP localport=1433
netsh advfirewall firewall add rule name="Open Port 1434" dir=in action=allow protocol=TCP localport=1434



##  or Open ports for Domain, Private  with PowerShell in ONE command:

New-NetFirewallRule -DisplayName 'HTTP Inbound' -Profile @('Domain', 'Private') -Direction Inbound -Action Allow -Protocol TCP -LocalPort @('80', '443')

 
 

 

https://blogs.msdn.microsoft.com/timomta/2016/11/04/how-do-i-open-ports-with-powershell/

Advertisements

System Administrator Was Locked Out from SQL Server Instance After Installation

Installed SQL Server Database Engine with a service account (not the admin user) and after installation finished, you cannot login to the installed instance with windows authentication.

How to allow the user (system administrator role) to access the instance?
Here are the steps:
When your start SQL Server in single-user mode with either -m or -f options, any member of computer’s local admins can then connect to the instance as a member of the sysadmin fixed server role.

Option 1: Powershell to run SQLcmd to add the login to sysadmin role (tested):

  
  
 # stop database service if it is running
net stop mssqlserver
#start database service as single user mode (for default instance mssqlserve)

net start mssqlserver /m

# in single user mode, window admin group member can access the instance
 
 & sqlcmd -S "myservername" -E -Q "EXEC sp_addsrvrolemember 'myDomain\myID', 'sysadmin';"
 
##After add the user, restart service without single user mode;
net stop mssqlserver
net start mssqlserver
 ## start agent service default instance
net start "SQL Server Agent (MSSQLSERVER)"
 ## to drop a login, not need here.
    & sqlcmd -S "myservername" -E -q "DROP LOGIN [myDomain\OtherID];"

 

Option 2 (tested)
2.1 From UI:
Open SQL Server Configuration Manager (run as admin if asked)>> Select SQL Server Services>>Click on your instance to open Properties window>>From Startup Parameters>> Specify a startup parameter
Add -m ( this is a dash and a lower case letter m). >>Click OK and Restart.
Make suer SQL Server Agent Service stopped.
Restart SSMS by Run as administrator and you will be in as a sysadmin now. Create a new login for your account and add your login as sysadmin role from SSMS.
2.2 Or using T-SQL script:

  

  CREATE LOGIN [yourDomain\yourUsername] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [yourDomain\yourUsername];


 

Close SSMS and restart SQL Server Configuration Manager and remove the startup parameter -m.
Restart all services now.

Launch SSMS under your user name (windows authentication), you should be connected now.

https://technet.microsoft.com/en-us/library/dd207004%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396


T-SQL to Convert CYYMMDD to Standard Date

  

 --For date between 1900 and 2099
--CYYMMDD
--7 digits
select   Cast(stuff('1170226',1,1,'20') as date) 
-- or
,  Cast(stuff('1170226',1,1
,Case When right('1170226',1)=1 then '20' When right('1170226',1)=0 then '19' End) 
as date) 


 

Binary to Bigint Sample

  

 Declare @bStr varchar(16)='09EEDF7C10C41030'


  
 Select Convert(BIGINT,0x09EEDF7C10C41030) --715755114748710960

 --Use CONVERT function with style 2 to data type BIGINT

Select  Convert(bigint, CONVERT(VARBINARY(8),  @bStr,2))  

--check
Select   CONVERT(BINARY(8), CONVERT(BIGINT, 715755114748710960))  
--0x09EEDF7C10C41030

--Below returns wrong value
Select  Cast('0x'+@bStr as binary(8))  ---wrong



 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ef5cd089-71c9-4ded-af69-8682d77595ed/hexadecimal-to-number-table-values-functionsp?forum=transactsql

http://stackoverflow.com/questions/33021992/i-want-to-get-hexadecimal-value-off-a-binary-value-removing-0x-from-it


Winscp Script to Rename a File to Append Timestamp

Based on a link from Stackoverflow by WinSCP author Martin Prikryl:
The put command accepts an optional second argument to specify a target name.
put localfile remotefile

http://stackoverflow.com/questions/41465186/batch-script-to-rename-file-with-timestamp-on-ftp-server-after-upload

Here is an example I used:

winscp.StandardInput.WriteLine(“put E:\\myfolder\\myfile.csv myfile” + DateTime.Now.ToString(“_MMddyyyy_HHmm”) + “.csv”);
The local file name is myfile.csv and the remote file name changed to
myfile_03202017_1413.csv

//Based on WinSCP site forum, Martin suggested: mv command should work as well
//winscp.StandardInput.WriteLine(“mv myfile.csv myfile”+ DateTime.Now.ToString(“_MMddyyyy_HHmm”) + “.csv”);


Powershell with Literal Path

When folder name has [], you can escape them with two back tick “ to escape them to create a file by using the path with special characters.

  
  
 
 
 
New-Item  –Path "c:temp\``[Profile Backup``]\$(Get-Date -Format yyyy_MM_dd)\" -Name "Installed_Programs1.txt" -ItemType File -Force

New-Item  –Path "c:temp\``[Profile Backup``]\2017_03_17\" -Name "Installed_Programs1.txt" -ItemType File -Force
 
 
 ##create the folder(don't need escape), then create the file
md -Path "c:temp\[Profile Backup]\$(Get-Date -Format yyyy_MM_dd)" -Force
New-Item  –Path "c:temp\``[Profile Backup``]\$(Get-Date -Format yyyy_MM_dd)\" -Name "Installed_Programs1.txt" -ItemType File -Force

 

https://technet.microsoft.com/en-us/library/ff730956.aspx

https://social.technet.microsoft.com/Forums/windows/en-US/27c62029-49c2-4170-864a-9c3781737a64/newitem-not-creating-file-why-not?forum=winserverpowershell


WASP Bar Code Reader (MCS 3950) Unofficial Setting to Read One Digit

By default, WASP Barcode ReaderWCS 3950 does not support 1 digit with Code128 barcode based on its User Manual.

But I found a link to have a workaround:
http://support.waspbarcode.com/index.php?/Knowledgebase/Article/View/884/74/program-the–wlr2900-wcs3900-and-wlr8900-series-scanners-to-read-pre-printed-barcodes

A link titled as “Code 128 Minimum Length Programming Sheet” has barcode sources to update scanner setting.

The source barcode for Minimum Length barcode, 0 barcode and 1 barcode was downloaded from below link:
http://dl.waspbarcode.com/wasp/supportfiles/WLR2900,WCS3900,WLR8900MinLength.pdf
After print this PDF page and do the following
1. Scan the Minimum Length barcode (Code 128).
2. Scan the 0 barcode then the 1 barcode.
3. Scan the Minimum Length barcode again.

You should be able to scan one digit barcode generated with code 128 now.