SQL Server Admin 2014 :: Deny SSMS Connections From Remote Servers?

Nov 20, 2014

Is there any way I can deny connection from SSMS only from remote servers?

View 7 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: SSMS Restarts Daily On Remote Desktop?

Dec 11, 2014

I'm using ssms 2014, but got the same problem with 2012. I use ssms almost entirely on remote desktop sessions ( Windows 7, Server 2008R2, Server 2012 ). It may be related to having filtered job activity monitor windows open for hours, but about once per day ssms fails, and has to restart. Upon resuming usually only one of several queries is restored.

View 8 Replies View Related

SQL Server Admin 2014 :: Remote Connections Not Establishing

Mar 26, 2015

I am getting problem with remote login to the sql instance.

In machine Sql 2008R2 is installed as default later 2014 installed as named instance. Both TCP/IP, shared memory, namedpipes are enabled for both named & default instances. For the default instance I can used the remote connections but the named instance is not allowing remote connections.

View 5 Replies View Related

SQL Server Admin 2014 :: Does Security-admin Role Plus Deny Alter Any Login Cancel Each Other Out

Aug 27, 2015

I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.

I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.

I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).

So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.

View 0 Replies View Related

SQL Server Admin 2014 :: Deny Permission For Object

Feb 4, 2015

How can we deny an Object select Permission which have Sysadmin role.

View 2 Replies View Related

SQL Server Admin 2014 :: Deny Access To AD Login For Certain Period Of Time

Apr 23, 2015

SQL server job or SP to deny access to an AD login for certain period of time to SQL server instance...i.e. to deny access to login ADxyz from 12 PM to 10 PM and revoke access to same login at 10:01 PM...

View 3 Replies View Related

SQL Server Admin 2014 :: SSMS Will Only Run As Administrator

May 28, 2015

After installing SSMS on some computers - the only way we can get SSMS to run correctly is to run it as the administrator. Is there a way where you don't have to do that? These end users are logging as themselves and have accounts in SQL Server all set up - but SSMS will only launch for them if we right click and select "run as administrator".After doing some digging - it seems that this is a common problem out there.

View 3 Replies View Related

SQL Server Admin 2014 :: Restriction On DML Statements In SSMS

Apr 27, 2014

I would like to know if there is any option to Restrict DML statements in SSMS for a user where the same user should be able to perform these actions through application on particular database.

View 1 Replies View Related

SQL Server Admin 2014 :: SSMS - The Application Cannot Start

Jul 4, 2014

Has experienced this error when trying to open SSMS? Was working fine earlier. I have tried uninstall/reinstall, a few reboots,repairing the install, etc.

View 5 Replies View Related

SQL Server Admin 2014 :: SSMS Occasionally Freezing / Hanging

Jul 7, 2015

I have a weird intermittent issue with an enterprise version of SS2014. When clicking or right clicking around SSMS will lock up and display the 'SSMS is busy - waiting for an internal operation to complete'. It is only specific to the server as when I connect using my local SSMS this doesn't happen. This was happening both pre and post SP1 install.

View 4 Replies View Related

SQL Server Admin 2014 :: Permissions To Debug Stored Procedures Using SSMS?

Jun 25, 2015

What permission is required to run debug feature in SSMS(debug Stored Procedures). This is a development machine and developer requested for this.

EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'.

EXECUTE permission was denied on object 'sp_sql_debug', database 'master'.

Is there any option other than giving sysadmin privilege on SQL?

View 0 Replies View Related

SQL Server Admin 2014 :: SSMS - Disable Check For Memory Optimized Tables?

Oct 2, 2014

I have the following setup:

- An MSSQL 2014 Standard server that houses multiple small databases (in excess of a hundred).
- These databases are frequently dropped and restored by an application that uses this SQL Server.
- There is a business need for this setup at this time, so I can't get away from it. Therefore answers like "don't have so many small databases that are frequently dropped and restored" would be somewhat unuseful

This is the problem I have:

- When I connect SSMS 2014 to the server and expand the "Databases" node, it takes forever to display. In comparison, SSMS 2008 connected to SQL 2008R2 server with the same number of databases displays the Databases tree very quickly.

I ran a trace to see what exactly SSMS 2014 is doing. When the "Databases" node is expanded, it runs a query that checks each database for Memory-Optimized Tables (new and wonderful feature of SQL 2014 for sure, but I'm not using it, at least yet). Naturally, when you have to loop through over a hundred DBs, it takes time. Worse yet, if one of these DBs is in process of being restored, the query sits and waits to time out before proceeding to the next DB. Sometimes this causes outright timeouts. Here is the query:

use [MyDatabase]
SELECT
ISNULL((select top 1 1 from sys.filegroups FG where FG.[type] = 'FX'), 0) AS [HasMemoryOptimizedObjects]

To be sure, this is NOT a SQL Server performance issue. This server processes a rather heavy workload and has been doing so for over a month, and the workload completes within expected time limits or better. Even so I've done some basic performance measuring, and the server itself is quite all right.

Moreover, if I connect SSMS 2008 to it, I get an error message (Index out of bounds or somesuch), but SSMS 2008 does connect, and displays the Databases tree much faster than SSMS 2014.

I'd like to turn off the option to check for Memory Optimized Objects altogether, as I'm not using the feature.

View 3 Replies View Related

SQL Server Admin 2014 :: Query Multiple Servers With A Scheduled Job Using CMS?

Mar 13, 2014

I can easily query multiple servers using the multi-server query function in Central Management Server and write some of the results to logging tables. I would like to be able to do this via a scheduled job. So far I am finding that even setting up Master/Target Servers this may not work and the only workaround is either using SSIS, SQLCMD (by basically hard coding the servername) and possibly Powershell.

tell me if they have been successful just using standard jobs and querying against multiple servers?

If I can't save the results to a 'central' database/table (I can do this when in SSMS), but can still query against multiple servers I was thinking I could write the results to a CSV file that a SSIS job picks up.

I have attempted using SSIS to iterate through servers and have been plagued with intermittent connection issues when using a For...Loop container.

View 1 Replies View Related

SQL Server Admin 2014 :: How To Close Active Connections Or Create Connection Timeouts

Dec 9, 2014

we have roughly 22 people connected to one database. But after a while, their applications begin to drag due to in and out communication with the server. When i check the active connections on the sql server, some times i see 157 active connections, please how to i set a timeout or connection interval close, so as reduce the heavy load being put on the server. Or how can i automatically close connections when they get higher than 50 connections.

This settings should be sql server 2008 related.

View 5 Replies View Related

SQL Server Admin 2014 :: How To List The Target Servers Associated With A Master Server

Sep 9, 2015

I'm looking for a way to list the target servers associated with a master server. The reason is that we're moving to another master server, and I'd prefer not to move the targets manually.

I've got most of the T-SQL already (sp_msx_enlist, sp_add_jobserver), but I'd like a scripted solution instead of a wizard.

View 2 Replies View Related

SQL Server Admin 2014 :: Cannot Connect To Named Instance (2nd Instance) From Local SSMS

Jul 22, 2015

I've two instances(Default, Named[dynamicsFINANCE]) running on SQL server 2014. However, when I try to connect to named instance say (dynamicsFINANCE) using SQL authentication from local SSMS, I get below error message:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I assigned a static port number to the named instance [dynamicsFINANCE] 1450. I also setup the firewall rule to allow access to Port 1450.

View 5 Replies View Related

SQL Server Admin 2014 :: Remote Database Connectivity Error

Apr 17, 2014

We are in web site development company,Previously we don't have proxy configuration, after implementing Proxy , we have an issue to connect a remote database.

The error pops "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. Error 53".

View 1 Replies View Related

SQL Server Admin 2014 :: Remote Access To Named Instance

Jul 6, 2015

I try to connect from a pc to a SQL Server on another pc. Both pc’s are in a workgroup. I want to connect from a Windows Forms application to a named instance on the other computer. By now I have been able to connect from one pc to SQL Server on the other with tcp:smurfin, 52782.

I want to be able to use servernameinstancename (instead of portnumber) to make a connection in a Windows Forms application.

I’ve checked / tried te following:

•In the properties of the instance, tab Connections, the option Allow Remote Connections is enabled
•In Configuration Manager: TCP is enabled
•The service SQL Server Browser is started
•On the tab IPAddresses, in the section IPAll, there is NO portnumber for TCP Port. And TCP Dynamic Ports has the nummer 52782
•I have created un inbound rule for port 52782 and also for 1434 (SQL Server Browser). And to be on the save side: a rule for 1433 as well.
•Restarted the service

If I run the following code in SQL Server, that same port number (52782) is returned:

EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO
SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID

[Code] ....

View 5 Replies View Related

SQL Server Admin 2014 :: Huge Traffic Between Share Point Front End Servers To Content Database?

Feb 20, 2014

We have a Customized share point application with Very minimal data usage and we have used only 5 to 6 lists and libraries only in the share point.

Configuration is

Clients -- fire wall --- Load Balancer ---- WF1 and WF2 --- SQL DB

ROUTING IS VIA FIRE WALL.

SUDDENLY THE SITE GOT DEAD SLOW AND UNABLE TO TRACE THE PROBLEM AS EVERY THING LOOKS FINE.

Checked with the firewall Team and they stated its fine from their end & even we have verified the counters, CPU, Memory & Page life expectancy, buffer counters all looks good and even we do not have huge data in the database. We have only 50 concurrent users are working...

View 2 Replies View Related

SQL Server Admin 2014 :: Setup Performance Monitor Collector On Workstation Collecting Remote Server Data?

Mar 31, 2015

I set up the collector, and specify the Run As as my AD account in the Collector Set - Properties - General screen. My AD account is the local admin of the remote server.

However, the collector does not seem to work. Although the collecting set is shown as running, the The blg file stays at 64K. If I open it, there is nothing inside (no counter at the bottom). What did I miss?

View 1 Replies View Related

SQL Server 2008 :: Set Up Log Shipping To 2 DR Servers Instead Of One Via SSMS GUI?

Sep 15, 2015

I have to set up log shipping from Prod server "A" to 2 different DR servers ("B" and "C")...What do i have to do differently (or additional) using the GUI (ie not using Tsql Scripts) to accomplish this, in addition to the steps that are done to log ship to just one DR server?

View 0 Replies View Related

Network Timeout To Mirror Causes Primary To Deny Connections

Nov 1, 2006

We've had two instances now where when there is a network connection timeout to the mirror from the primary, the primary db server goes to 100% utilization and refuses all connections.

The first time we had to reboot the primary, the 2nd time mirroring picked up again 10 minutes later.

There are two dbs being mirrored, one is 15gig, the other 4gig. Both boxes are running SQL2005 64 bit and Win 2003 64bit.

This happened at 6am and typically there shouldn't be a lot of traffic at that time but here are the error messages in the SQL log below.

We are going to try and move db communications to a separate network and network card - but this looks like either a bug in mirroring or a configuration problem on our end - though it works just fine other times.

Any thoughts/suggestions would be greatly appreciated.

Thanks!

Mark

SQL Error Log:

11/01/2006 06:12:12,Logon,Unknown,The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications<c/> depending on how the administrator has configured the server. See Books Online for information on this error message: 0x2746. [CLIENT: 10.16.7.7]
11/01/2006 06:12:12,Logon,Unknown,Error: 17194<c/> Severity: 16<c/> State: 1.
11/01/2006 06:12:12,Logon,Unknown,The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications<c/> depending on how the administrator has configured the server. See Books Online for information on this error message: 0x2746. [CLIENT: 10.16.7.7]
11/01/2006 06:12:12,Logon,Unknown,Error: 17194<c/> Severity: 16<c/> State: 1.
11/01/2006 06:12:12,Logon,Unknown,The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications<c/> depending on how the administrator has configured the server. See Books Online for information on this error message: 0x2746. [CLIENT: 10.16.7.2]
11/01/2006 06:12:12,Logon,Unknown,Error: 17194<c/> Severity: 16<c/> State: 1.
11/01/2006 06:12:11,spid22s,Unknown,Database mirroring connection error 4 '10054(An existing connection was forcibly closed by the remote host.)' for 'TCP://PYTHAGORAS.test.com:7024'.
11/01/2006 06:12:11,spid22s,Unknown,Error: 1474<c/> Severity: 16<c/> State: 1.
11/01/2006 06:04:53,spid26s,Unknown,Database mirroring is inactive for database 'NewScribe'. This is an informational message only. No user action is required.
11/01/2006 06:04:53,spid26s,Unknown,The mirroring connection to "TCP://PYTHAGORAS.test.com:7024" has timed out for database "NewScribe" after 10 seconds without a response. Check the service and network connections.
11/01/2006 06:04:53,spid26s,Unknown,Error: 1479<c/> Severity: 16<c/> State: 1.
11/01/2006 06:04:53,spid24s,Unknown,Database mirroring is inactive for database 'HL7Transfer'. This is an informational message only. No user action is required.
11/01/2006 06:04:53,spid24s,Unknown,The mirroring connection to "TCP://PYTHAGORAS.test.com:7024" has timed out for database "HL7Transfer" after 10 seconds without a response. Check the service and network connections.
11/01/2006 06:04:53,spid24s,Unknown,Error: 1479<c/> Severity: 16<c/> State: 1.

View 12 Replies View Related

Deny Remote Logins?

Sep 17, 2002

I was wondering if there is anyway to deny remote logins on SQL Server 7 (other then pulling the network cable out of the back :) ) so that nothing can login during a service pack upgrade?


Many thanks in advance
Jason

View 6 Replies View Related

SQL Server 2014 :: Error Running Stored Procedure From SSIS Runs Fine In SSMS?

Mar 23, 2015

I have simple query which creates tables by passing database name as parameter from a parameter table .

SP1 --> creates databases and calls SP2--> which creates tables . I can run it fine via SSMS but when I run it using SSIS it fails with below error .The issue gets more interesting when it fails randomly on some database creation and some creates just fine .

Note** I am not passing any database of name '20'

Exception handler error :

ERROR :: 615 :: Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ---------------------------------------------------------------------------------------------------- SPID: 111 Origin: SQL Stored Procedure (SP1) ---------------------------------------------------------------------------------------------------- Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ----------------------------------------------------------------------------------------------------

Error in SSIS

[Execute SQL Task] Error: Executing the query "EXEC SP1" failed with the following error: "Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.I have sysadmin permission .

View 6 Replies View Related

To Maintain Two Different Connections For Different Servers In ASP.Net Application(sql Server And Oracle)

Oct 10, 2007

I am working in ASP.NET 2.0 and using sql server 2000 as backend . In my application i need to insert/update to oracle database table lying on different server. Please let me know how can i maintain two different connecttions to different databases lying on different servers..... 

View 1 Replies View Related

To Maintain Two Different Connections For Different Servers In ASP.Net Application(sql Server And Oracle)

Oct 10, 2007

I am working in ASP.NET 2.0 and using sql server 2000 as backend . In my
application i need to insert/update to oracle database table lying on
different server. Please let me know how can i maintain two different
connecttions to different databases lying on different servers.....

View 1 Replies View Related

Server Does Not Allow Remote Connections

Jul 12, 2007

 Hi folks,Perhaps every 100 calls to DB i get 1 error message like this:"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" i am using SQL 2000 and am wondering why this happens.I appreciate your help.Leo. 

View 3 Replies View Related

SQL Server Does Not Allow Remote Connections

Oct 11, 2006


Hello ,

The problem is that we are having Problem while making the remote Connection to the Sql Server 2005

Basically We are having a problem connecting to SQL2005 through a package remotely. when we try to connect to a remote data base, we get the following error

[Connection manager "CRA LRRS Development DB"] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ".


Although by running the configuration manager on the Remote DataBase System we have enable the Remote Connections through name and IP both but still it cant access it.

Please tell us the possible consequences and provide us the feedback.

I will be really obliged if any of you can provide us the feedback on it as soon as possible.

Thanking You,
Yours Sincerely,
Saima Salim

View 1 Replies View Related

SQL Server Does Not Allow Remote Connections

Apr 7, 2008

I am having trouble with Report Manager. It seems that every day or every other day we get this message:

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."

To fix this we open the Surface Area Configuration for the server that hosts the Reporting Database and stop and restart the database engine then open up the Surface Area Configuration for the server that hosts the Report Manger database and stop and restart that database engine along with the Reporting Services Browser.

This actually may not be the exact order that we do the stop/starts because we have tried so many different combinations (ie. stop one then start another then restart that one, etc.). Anyway, we usually keep on stoping and restarting until we can get back into Report Manager without getting the error message from above.

Does anyone have any insight as to what this could be??

View 4 Replies View Related

SQL Server Does Not Allow Remote Connections

Sep 21, 2006

I downloaded VS studio express, and created a personal website using the wizard to test drive the thing. When I hit ctr + f5 to run the sample provided, then I get this message:

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) "

I tried disabling the firewall (Norton's firewall because Windows is disable always). Any suggestions? This is a brand-new machine, running XP sp2, just downloaded the VS studio express.

I really appreciate your inputs.

Regards,

Carlos

info@paesano.com

View 4 Replies View Related

SQL Server Does Not Allow Remote Connections. (

Dec 8, 2006

Hi,

Attempting to open a new database engine from a Windows 2003
R2 server (x64) which is

running SQL Server 2005 to my desktop PC (XP Pro) which is running SQL 2005 developer
edition.



I€™ve opened up TCP/IP and named pipes and enabled all protocols
within surface area configuration and server

configuration manager but get this€¦



An error has occurred while establishing a connection to the
server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default settings
SQL Server does not allow remote connections. (provider: TCP Provider, error: 0
- A connection attempt failed because the connected party did not properly
respond after a period of time, or established connection failed because
connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)



Is this a red-herring type message? I ask because the text
states €œthis failure may be caused€? and as far as I can see I€™ve open up my

PC to all connections.



Thanks in advance



Dave

View 10 Replies View Related

Ql Server Does Not Allow Remote Connections

Jul 2, 2007

hi,

sql server 2005 express is installed on comp1. my client application is installed on comp2 and comp3. all comps have win xp. when i run my application, it can't connect to sql server express. i got the following message:

an error has occured while establishing a connection to the server. when connecting to sql server 2005, the failure may be caused by the fact that under the default settings sql server does not allow remote connections. (provider: sql network interfaces, error 26 - error locating server/instance specified)


so, how can i solve this problem, to connect to the server?

View 4 Replies View Related

SQL Server Does Not Allow Remote Connections

Dec 18, 2007

I've got the following error from running my aspx webpages, trying to communicate to the MSSQL 2005 database.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)


I've see and read a lot of chatter on the forum on the topic, but didn't find my solution. I have done a number of things, all of which didn't shake the error.
1) I went into SQL Server Surface Area Configuration for Services and Connections, and set Remote Connections to Local and Remote Connections, using TCP/IP only
2) Disabled Firewall
3) Made exceptions throught the Firewall for SQL Server and SQL Browser


Any other suggestions, thanks in advance.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved