Advertisements

Archive

Archive for the ‘SQL Errors’ Category

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


 
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.


Advertisements
Categories: SQL Errors

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

June 10, 2017 5 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\

 

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

SQL Error – SQL Server blocked access to STATEMENT ‘OpenRowset/ OpenDatasource’ of component ‘Ad Hoc Distributed Queries’

December 25, 2016 Leave a comment

 
Today while executing a Stored Procedure which internally executes a remote query via Linked Server, I got following error:

Msg 50000, Level 16, State 127, Procedure spExecureRemoteQuery, Line 50

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

 

The above clearly means that the “Ad Hoc Distributed Queries” option is disabled on the database instance.

–> Run below Query to check if this property is disabled, if will show you 0 value under Config & Run value columns:

sp_configure 'show advanced options', 1
GO
EXEC sp_configure
GO

adhocdistributedqueries
 

–> To enable it run the below Query:

sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
EXEC sp_configure
GO

adhocdistributedqueries-set

…the value under Config & Run value columns shows 1, means that now the Property is enabled and you can execute your Remote/Linked-Server queries.


SQL Error – The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)

December 24, 2016 2 comments

 
I was trying to export a SQL table to Excel and I got below error:

TITLE: SQL Server Import and Export Wizard
——————————
The operation could not be completed.
——————————
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)
——————————

 

sqltoexcel-error
 

After searching a bit I came to know that the above provider is not installed, and I need to install the Microsoft Access Database Engine setup, to facilitate the transfer of data between existing Microsoft Office files such as “Microsoft Office Access 2010” (*.mdb and *.accdb) files and “Microsoft Office Excel 2010” (*.xls, *.xlsx, and *.xlsb) files to other data sources such as “Microsoft SQL Server”.

–> Get the Microsoft Access Database Engine 2010 Redistributable, [link].

There will be two files:

1. AccessDatabaseEngine (for. 32-bit)

2. AccessDatabaseEngine_x64 i.e. 64-bit

If your SQL Server is 32 bit, then install just the first one.
But if its 64 bit, then install both, first 32 bit & then 64 bit.


Categories: Excel, SQL Errors Tags:

SQL Error – SQL Server service failed to Start | Windows could not start the SQL Server

December 15, 2016 3 comments

 
Today I was trying to connect to a SQL Server instance of my DEV machine from SSMS, it took bit more time than usual and threw me an error:

Cannot connect to [SQL_Instance_name], 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 Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

 

I immediately checked SQL Services, and yes the SQL Server service was not running. But when I tried to run the service it didn’t turn up and it gave me an error:

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 14.

sqlservicesdown
 

Now, the only thing to know what would have happened is the Event viewer.

–> You can open “Event Viewer” by any of the below options:

1. Shortcut: Eventvwr.msc

2. Type “Event Viewer” in search box on Windows 8 and above.

3. Open Control Panel –> System and Maintenance –> Administrative Tools –> Event Viewer
 

Now on the “Event Viewer” window go to: Windows Logs –> Application

Check the logs on the General or Details tab:

eventviewer-for-sqlserver

I checked all errors and it threw following errors in sequence:

Error: 49910, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Error: 33086, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
 

Error: 701, Severity: 17, State: 65. There is insufficient system memory in resource pool ‘internal’ to run this query.
 

–> So, if you query these errors in sys.messages then you will see that one of the error (id = 17300) is related to insufficient memory:

select * 
from sys.messages 
where language_id = 1033 
and message_id IN (49910,17312, 17300, 33086)

SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option ‘user connections’ to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.

 

–> Let’s also check SQL Server ERROR LOGS, go to Error Log location on you SQL Server machine: C:\Program Files\Microsoft SQL Server \MSSQL11.MSSQLSERVER \MSSQL\Log

I’ve removed unnecessary part here as the error log was too long, highlighted the real cause of the issue, and that is: Failed allocate pages: FAIL_PAGE_ALLOCATION 1.


2016-12-15 09:34:14.82 Server SQL Server detected 2 sockets with 10 cores per socket and 10 logical processors per socket, 20 total logical processors; using 20 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2016-12-15 09:34:14.82 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2016-12-15 09:34:14.82 Server Detected 143359 MB of RAM. This is an informational message; no user action is required.

2016-12-15 09:34:15.27 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2016-12-15 09:34:15.27 Server Lock partitioning is enabled. This is an informational message only. No user action is required.
2016-12-15 09:34:20.30 Server Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2016-12-15 09:31:14.07 Server
Process/System Counts Value
—————————————- ———-
Available Physical Memory 145982603264
Available Virtual Memory 140678812086272
Available Paging File 167077097472
Working Set 337784832
Percent of Committed Memory in WS 100
Page Faults 94990
System physical memory high 1
System physical memory low 0
Process physical memory low 1
Process virtual memory low 0
2016-12-15 09:31:14.07 Server Error: 49910, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

2016-12-15 09:31:14.08 Server
MEMORYCLERK_SQLGENERAL (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1240
2016-12-15 09:31:14.08 Server Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2016-12-15 09:31:14.08 Server SQL Server shutdown has been initiated

2016-12-15 09:31:14.08 Server
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 48
2016-12-15 09:31:14.08 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

2016-12-15 09:31:14.08 Server
MEMORYCLERK_SNI (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2016-12-15 09:31:14.08 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

MEMORYCLERK_SQLXP (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2016-12-15 09:31:14.08 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

2016-12-15 09:31:14.08 Server
MEMORYCLERK_SOSNODE (Total) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 26888
2016-12-15 09:31:14.08 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2016-12-15 09:31:14.08 Server
MEMORYCLERK_SOSOS (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
2016-12-15 09:31:14.08 Server Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
MEMORYCLERK_SOSMEMMANAGER (node 0) KB
—————————————- ———-
VM Reserved 480
VM Committed 336
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2016-12-15 09:31:14.08 Server SQL Server shutdown has been initiated

2016-12-15 09:31:14.08 Server
MEMORYCLERK_XE (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 376
2016-12-15 09:31:14.08 Server Error: 33086, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2016-12-15 09:31:14.08 Server
MEMORYCLERK_SQLLOGPOOL (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 152
2016-12-15 09:31:14.08 Server Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

2016-12-15 09:31:14.08 Server SQL Server shutdown has been initiated
2016-12-15 09:31:14.08 spid14s Error: 701, Severity: 17, State: 107.
2016-12-15 09:31:14.08 spid14s There is insufficient system memory in resource pool ‘internal’ to run this query.

In the Error Log above you can also see the Error IDs we saw in Event Log, I’ve highlighted them with the Memory Clerks.
 

–> Now let’s wee if we can start SQL Server with minimal configuration by applying the Startup Parameter “-f”:

Open SQL Server Configuration Manager (SSCM) –> select “SQL Server Services” –> right click on SQL Server service, and select Properties.

sqlserverconfigmgr

Add “-f” as Startup Parameter, as shown below.

startupparam-f

Again go back to the SSCM and Start the SQL Server service, this time it will start as you have set SQL Server to run with minimum configuration, and thus it will run on limited memory.
 

–> Now I opened the SSMS and connected to the respective instance, under Object Explorer right click on Instance name, and selected Properties. Moved to the Memory page and checked the Maximum server memory (in MB) setting. It was just 128 MB, so I increased it to 110 GB as my server RAM was 140 GB.

sqlservermemory
 

–> Again went to the SSCM and removed the Startup Parameter “-f”, and restarted SQL Server services.

Now I was able to login to SQL Server instance without any issues !!!