Advertisements

Archive

Archive for the ‘SQL Errors’ Category

SQL Job creation failing (having VBScript step) after SQL Server 2016/2017 upgrade

May 21, 2018 Leave a comment

 
Yesterday my friend pinged me and told that he is facing some issues while executing a SQL Job DDL script. They had upgraded their SQL Server version from 2008 to 2016, and while creating SQL Jobs they were facing below error:

Msg 14234, Level 16, State 1, Procedure sp_verify_subsystem, Line 28 [Batch Start Line 2]
The specified ‘@subsystem’ is invalid (valid values are returned by sp_enum_sqlagent_subsystems).

 
I checked the code and on inquiring for a moment I saw that one of the SQL Job step was configured for a VB Script, as shown below:

...
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep 
@job_id=@jobId, 
@step_name=N'xyz VBScript', 
@step_id=3, 
@cmdexec_success_code=0, 
@on_success_action=4, 
@on_success_step_id=0, 
@on_fail_action=5, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, 
@subsystem=N'ActiveScripting', 
...

On quickly checking on net I came to know that this feature has been discontinued and should not be used.

** Important *\* This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
MS BoL link

 

This MS BoL link also mentions about the discontinued feature:

ActiveX subsytem is discontinued. Use command line or PowerShell scripts instead.


Advertisements

SQL Error – Cannot open backup device ‘https://xyz.blob.core.windows.net/xyz/xyz.bak’. Operating system error 50(The request is not supported.).

February 6, 2018 1 comment

 
Today I got an email from someone regarding an issue he was facing. He was trying to Backup SQL Server database to URL i.e. the Azure Blob Storage, by issuing below Backup command:

USE [master]
GO

BACKUP DATABASE [ManTest]
 TO  URL = N'https://mantestaz.blob.core.windows.net/sqlbackups/mantest.bak'
 WITH 
	COMPRESSION  
    ,STATS = 5;  
GO

And he was getting following error:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device
‘https ://mantestaz.blob.core.windows.net/sqlbackups/mantest.bak’.
Operating system error 50(The request is not supported.).

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

 

By checking the above error, you can make out that we are not able to connect to the Azure Storage resource due to some access issues. I checked online and found that you need to create Credentials on SQL Server from where you want to access the Azure Storage resource, and there are few methods for the same:
 

–> Method #1: Using storage account identity and Access Key

Azure Access Keys authenticates your applications when making requests to the Azure storage account. You will have to create a Credential on SQL Server end by providing the Access Key from your Azure Storage account, check below:

USE [master]
GO

CREATE CREDENTIAL [DBBackupCred] 
WITH IDENTITY = 'mantestaz'  -- Storage Account Name
,SECRET = 'xyx35HWTOnkDpiHkNWayz2Gsw6Figyxyx=='; -- Access key
GO

– Now you can fire the BACKUP command with the additional WITH CREDENTIAL option so that the Access can be authenticated for storing and writing backups on Azure Blob Container:

USE [master]
GO

BACKUP DATABASE [ManTest]  
 TO  URL = N'https://mantestaz.blob.core.windows.net/sqlbackups/mantest.bak' 
     WITH CREDENTIAL = 'DBBackupCred'   
    ,COMPRESSION  
    ,STATS = 5;  
GO

38 percent processed.
77 percent processed.
99 percent processed.
Processed 328 pages for database ‘ManTest’, file ‘ManTest’ on file 1.
100 percent processed.
Processed 3 pages for database ‘ManTest’, file ‘ManTest_log’ on file 1.
BACKUP DATABASE successfully processed 331 pages in 15.792 seconds (0.163 MB/sec).

… and its done successfully.
 

–> Method #2: Using Shared Access Signature (SAS)

A Shared Access Signature (SAS) is a URI that grants restricted access rights to Azure Storage resources. You can provide a shared access signature to clients who should not be trusted with your storage account key but whom you wish to delegate access to certain storage account resources. By distributing a shared access signature URI to these clients, you grant them access to a resource for a specified period of time.

Here is the syntax to create SAS Credential:

USE [master]
GO

CREATE CREDENTIAL 
	[https://<storageaccountname>.blob.core.windows.net/<containername>] 
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
  SECRET = '<SAS_TOKEN>'; 

SQL Error – SQL Server Installation fails | Attributes do not match. Present attributes (Directory, Compressed, NotContentIndexed)…

January 9, 2018 1 comment

 
Few days back I got this query from a developer who was trying to install SQL Server, it was not getting installed and was giving below error:

Microsoft.SqlServer.Configuration.Sco.DirectoryAttributesMissmatch: Attributes do not match. Present attributes (Directory, Compressed, NotContentIndexed) , included attributes (0), excluded attributes (Compressed, Encrypted).


 

–> RCA: As the above error message mentions that the Directory is Compressed, it gives you an idea that the SQL Server installation is not supported on compressed drives. So, if you are installing SQL Server on a drive which has compression enabled it will give you this error.
 

–> Fix: You have to check the Drive properties and see if Compression is enabled, like shown below and un-compress it by un-checking the option.


SQL Error – Cannot connect to xyz_sql_instance, due to remote firewall issue

June 11, 2017 Leave a comment

 
After creating a new Azure Windows Server VM with SQL Server installed, I was not able to connect it from my PC via SSMS. Everytime I tried to connect I was getting following error:

Cannot connect to xyz_sql_instance.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provier, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
The network path was not found


 

–> I checked online and found following command to enable the port, but it ran into error:

C:\windows\system32>netsh firewall set portopening TCP 1433 “SQLServer”

“… netsh firewall” is deprecated; use “netsh advfirewall firewall” instead.


 

–> So, lets execute the new netsh advfirewall firewall command and see what options it has:

C:\windows\system32>netsh advfirewall firewall /?

The following commands are available:

Commands in this context:
? – Displays a list of commands.
add – Adds a new inbound or outbound firewall rule.
delete – Deletes all matching firewall rules.
dump – Displays a configuration script.
help – Displays a list of commands.
set – Sets new values for properties of a existing rule.
show – Displays a specified firewall rule.

To view help for a command, type the command, followed by a space, and then type ?.

 

–> Now with above options we are clear that we have to add a new inbound firewall rule, so we will check how can we use the add option:

C:\windows\system32>netsh advfirewall firewall add /?

The following commands are available:

Commands in this context:
add rule – Adds a new inbound or outbound firewall rule.

 

–> Let’s apply the add rule option and see more options:

C:\windows\system32>netsh advfirewall firewall add rule /?

Usage: add rule name=
dir=in|out
action=allow|block|bypass
[program=]
[service=|any]
[description=]
[enable=yes|no (default=yes)]
[profile=public|private|domain|any[,…]]
[localip=any|||||]
[remoteip=any|localsubnet|dns|dhcp|wins|defaultgateway|
||||]
[localport=0-65535|[,…]|RPC|RPC-EPMap|IPHTTPS|any (default=any)]
[remoteport=0-65535|[,…]|any (default=any)]
[protocol=0-255|icmpv4|icmpv6|icmpv4:type,code|icmpv6:type,code|
tcp|udp|any (default=any)]
[interfacetype=wireless|lan|ras|any]
[rmtcomputergrp=]
[rmtusrgrp=]
[edge=yes|deferapp|deferuser|no (default=no)]
[security=authenticate|authenc|authdynenc|authnoencap|notrequired
(default=notrequired)]

Remarks:

– Add a new inbound or outbound rule to the firewall policy.
– Rule name should be unique and cannot be “all”.
– If a remote computer or user group is specified, security must be
authenticate, authenc, authdynenc, or authnoencap.
– Setting security to authdynenc allows systems to dynamically
negotiate the use of encryption for traffic that matches
a given Windows Firewall rule. Encryption is negotiated based on
existing connection security rule properties. This option
enables the ability of a machine to accept the first TCP
or UDP packet of an inbound IPsec connection as long as
it is secured, but not encrypted, using IPsec.
Once the first packet is processed, the server will
re-negotiate the connection and upgrade it so that
all subsequent communications are fully encrypted.
– If action=bypass, the remote computer group must be specified when dir=in.
– If service=any, the rule applies only to services.
– ICMP type or code can be “any”.
– Edge can only be specified for inbound rules.
– AuthEnc and authnoencap cannot be used together.
– Authdynenc is valid only when dir=in.
– When authnoencap is set, the security=authenticate option becomes an
optional parameter.

Examples:

Add an inbound rule with no encapsulation security for browser.exe:
netsh advfirewall firewall add rule name=”allow browser”
dir=in program=”c:\programfiles\browser\browser.exe”
security=authnoencap action=allow

Add an outbound rule for port 80:
netsh advfirewall firewall add rule name=”allow80″
protocol=TCP dir=out localport=80 action=block

Add an inbound rule requiring security and encryption
for TCP port 80 traffic:
netsh advfirewall firewall add rule
name=”Require Encryption for Inbound TCP/80″
protocol=TCP dir=in localport=80 security=authdynenc
action=allow

Add an inbound rule for browser.exe and require security
netsh advfirewall firewall add rule name=”allow browser”
dir=in program=”c:\program files\browser\browser.exe”
security=authenticate action=allow

Add an authenticated firewall bypass rule for group
acmedomain\scanners identified by a SDDL string:
netsh advfirewall firewall add rule name=”allow scanners”
dir=in rmtcomputergrp= action=bypass
security=authenticate

Add an outbound allow rule for local ports 5000-5010 for udp-
Add rule name=”Allow port range” dir=out protocol=udp localport=5000-5010 action=allow

 

–> OK, so this gives us more options and also samples at the end to construct our command. So we will us the above highlighted command to add port for our SQL Server, as shown below:

C:\windows\system32>netsh advfirewall firewall add rule name=”SQL Server Engine” protocol=TCP dir=in localport=1433 action=allow

C:\windows\system32>netsh advfirewall firewall add rule name=”SQL Server Browser” protocol=UDP dir=in localport=1434 action=allow
 

Now, I am able to connect remotely without any issues.


Categories: SQL Errors

SSMS error – The Visual Studio component cache is out of date. Please restart Visual Studio.

June 10, 2017 11 comments

 
Yesterday while opening SSMS I got an error message popup which mentions:

The Visual Studio component cache is out of date. Please restart Visual Studio.


 

On checking in internet I found a similar issue logged in MS Connect, and the workaround was to cleanup the application folder in the Windows-Temp folder, which is located at following folder location:

C:\Users\your_user_name_here\AppData\Local\Temp\

Here is the shortcut to go to the TEMP folder:


 

This issue vanished after deleting the SSMS folder. This could be due to the Visual Studio component cache can’t be written or can’t be wiped while in use.


Categories: SQL Errors