Wednesday, February 23, 2022

Map a drive in SQL SERVER 2014

Tags

 

Why map a drive in SQL server 2014?

1.Easy to make a backup on remote Nas server or Synology.

2.Easy to restore database.

3.keep database on NAS or Synology.

After we run Query below  you need to restart service SQL MSSQL in Service management.

Then log in  SQL Server 2014 with  sa user.Then you will see the drive that you map.


Map drive in sql server 2014

Use Master

GO

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE WITH OVERRIDE

GO

EXEC xp_cmdshell 'net use Z: \\10.0.0.10\e /user:domain\userName "password"'

EXEC XP_CMDSHELL 'net use Z:  \\10.11.0.10\database\SQL /USER:admin  "123456789"';

EXEC XP_CMDSHELL 'Dir Z:'

        Disable map network drive in sql server

EXEC XP_CMDSHELL 'net use Z: /delete'

Tuesday, February 8, 2022

How to fix database error in sql 2014 with DBCC

Tags

 

first we need to use below command to check what table error.

DBCC CHECKDB (UAT) WITH NO_INFOMSGS, ALL_ERRORMSGS

Next  we will see which table got error then we need to change database to single user mode in database properties or can use command below:

ALTER DATABASE UAT

SET SINGLE_USER;

 Then time to repair table :

DBCC CHECKTABLE('AT_INOUT', REPAIR_ALLOW_DATA_LOSS)

And then after repair all table error we need to use the first command again to make sure all table was repaired.

DBCC CHECKDB (UAT) WITH NO_INFOMSGS, ALL_ERRORMSGS

Then we need to check log database is it big or not.? if it big we can shrink it by below guide:

Right click -->Task-->Shrink-->File-->then click OK.


The L2TP Connection Attempt Failed Because the Security Layer Encountered a Processing Error Fix

Tags

 Are you using a VPN (Virtual Private Network) to access certain websites? Then, you may come across an error “the L2TP connection attempt failed because the security layer encountered a processing error” when you try to make a VPN connection. L2TP is a well-known connection used for encryption and protection of VPN. Some of the reasons why you may see this error are when the Microsoft CHAP v2 protocol is disabled or when the PPP settings are not configures properly. However, the good news is, the “the L2TP connection attempt failed because the security layer encountered a processing error” issue can be resolved. Let’s see how.

Method 1: Uninstall latest security updates

Recent Update may have caused this issue while breaking your VPN. for fixing this, you can uninstall your recent update.

1 – Search APPWIZ.CPL in Windows search box and click on it.

Appwiz Cpl Min

 

2 – Now, Uninstall most recent Microsoft Windows update by right clicking on the most recent Microsoft Windows update and then clicking on Uninstall.

kb:5009543 for Windows 10

kb:5009566 for Windows 11

Control Panel Programs And Features Uninstall An Update Right Click Uninstall

 

3 –Restart your computer

Method 2: Turn on the Microsoft CHAP v2 Protocol

 

Step 1: Right-click on Start menu and select Run to open the Run command window.

 

Start Right Click Run

 

Step 2: In the Run command search box, type ncpa.cpl and hit Enter to open the Network Connections window.

 

Run Command Ncpa.cpl Enter

 

Step 3: In the Network Connections window, right-click on your VPN connection and select Properties.

 

Network Connection Vpn Right Click Properties

 

Step 4: In the VPN Properties dialogue box, go to the Security tab and select the radio button next to Allow these protocols option.

Now, make sure that the box next to Microsoft CHAP Version 2 (MS-CHAP v2) is checked.

Press OK to save changes and exit.

 

Vpn Properties Security Allow These Protocols Microsoft Chap Version 2 (ms Chap V2) Ok

Now, try connecting to the VPN and you should not experience the “L2TP connection attempt failed” error again.

 

Method 3: Turn on the LCP Protocol Extensions

 

Step 1: Press the Win + R keys together on your keyboard to open the Run command box.

Step 2: Now, write ncpa.cpl in the Run command search field and press OK.

 

Run Command Ncpa.cpl Ok

 

Step 3: In the Network Connections window that opens, locate the VPN, right-click on it and select Properties.

 

Network Connection Vpn Right Click Properties

 

Step 4: This time, go to the Options tab in the VPN Properties window and click on the PPP Settings button.

 

Vpn Prorperties Options Ppp Settings

 

Step 5: Next, in the PPP Settings dialogue box, check the box next to Enable LCP extensions.

Press OK to save the changes and exit.

 

Ppp Settings Enable Lcp Extensions Ok

 

You can now try connecting the VPN and it should get connected without showing the error.

Method 4: By Restarting IPSec Service

 

Step 1: Press the Win + X hotkey on your keyboard and select Run to open the Run command.

 

Win + X Run

 

Step 2: In the Run command window, type services.msc and hit Enter.

 

Run Command Services.msc Enter

 

Step 3:This will open the Service manager window. Go to the right side of the Service manager and under the Name column, look for IKE and AuthIP IPSec Keying Modules.

If it shows running, right-click on it and select Restart.

 

Services Right Side Name Ike And Authip Ipsec Keying Modules Right Click Restart

 

Step 4: Now, double-click on it and in the Properties window, under the General tab, go to the Startup type section.

Set the field to Automatic.

Press Apply and then OK to save changes and exit.

 

Properties General Startup Type Automatic Apply Ok

 

Step 5: Repeat Steps 3 and 4 for IPSec Policy Agent service.

 

Services Name Ipsec Policy Agent

 

Now, restart your VPN connection and it should start working fine now.

 

Method 5: By Reinstalling the Network Adapter

 

Step 1: Right-click on Start menu and select Run. This will open the Run command window.

 

Start Right Click Run

 

Step 2: In the Run command box, type devmgmt.msc and hit Enter.

 

Run Command Devmgmt.msc Enter

 

Step 3: In the Device Manager window that opens, navigate to Network adapters and expand the section.

Locate the network adapter for your VPN, right-click on it and select Uninstall device.


 

Device Manager Network Adapters Vpn Adapter Right Click Uninstall Device

 

Step 4: In the prompt that appears, click on the Uninstall button to confirm the action.

 

Once uninstalled, restart your PC. The device gets reinstalled automatically and gets reset to its default settings.

 

You must also check that proper certificate is being used for both the client and the server. Make sure that PSK (Pre Shared Key) if used, is the same and is configured for both the client and the VPN server.