SQL Server Admin 2014 :: Deny SSMS Connections From Remote Servers?
Nov 20, 2014Is 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, 2014I'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 RelatedSQL Server Admin 2014 :: Remote Connections Not Establishing
Mar 26, 2015I 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.
SQL Server Admin 2014 :: Does Security-admin Role Plus Deny Alter Any Login Cancel Each Other Out
Aug 27, 2015I 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.
SQL Server Admin 2014 :: Deny Permission For Object
Feb 4, 2015How can we deny an Object select Permission which have Sysadmin role.
View 2 Replies View RelatedSQL Server Admin 2014 :: Deny Access To AD Login For Certain Period Of Time
Apr 23, 2015SQL 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 RelatedSQL Server Admin 2014 :: SSMS Will Only Run As Administrator
May 28, 2015After 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 RelatedSQL Server Admin 2014 :: Restriction On DML Statements In SSMS
Apr 27, 2014I 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 RelatedSQL Server Admin 2014 :: SSMS - The Application Cannot Start
Jul 4, 2014Has 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 RelatedSQL Server Admin 2014 :: SSMS Occasionally Freezing / Hanging
Jul 7, 2015I 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 RelatedSQL Server Admin 2014 :: Permissions To Debug Stored Procedures Using SSMS?
Jun 25, 2015What 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?
SQL Server Admin 2014 :: SSMS - Disable Check For Memory Optimized Tables?
Oct 2, 2014I 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.
SQL Server Admin 2014 :: Query Multiple Servers With A Scheduled Job Using CMS?
Mar 13, 2014I 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.
SQL Server Admin 2014 :: How To Close Active Connections Or Create Connection Timeouts
Dec 9, 2014we 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.
SQL Server Admin 2014 :: How To List The Target Servers Associated With A Master Server
Sep 9, 2015I'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.
SQL Server Admin 2014 :: Cannot Connect To Named Instance (2nd Instance) From Local SSMS
Jul 22, 2015I'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.
SQL Server Admin 2014 :: Remote Database Connectivity Error
Apr 17, 2014We 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".
SQL Server Admin 2014 :: Remote Access To Named Instance
Jul 6, 2015I 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] ....
SQL Server Admin 2014 :: Huge Traffic Between Share Point Front End Servers To Content Database?
Feb 20, 2014We 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...
SQL Server Admin 2014 :: Setup Performance Monitor Collector On Workstation Collecting Remote Server Data?
Mar 31, 2015I 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?
SQL Server 2008 :: Set Up Log Shipping To 2 DR Servers Instead Of One Via SSMS GUI?
Sep 15, 2015I 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 RelatedNetwork Timeout To Mirror Causes Primary To Deny Connections
Nov 1, 2006We'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.
Deny Remote Logins?
Sep 17, 2002I 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
SQL Server 2014 :: Error Running Stored Procedure From SSIS Runs Fine In SSMS?
Mar 23, 2015I 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 .
To Maintain Two Different Connections For Different Servers In ASP.Net Application(sql Server And Oracle)
Oct 10, 2007I 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 RelatedTo Maintain Two Different Connections For Different Servers In ASP.Net Application(sql Server And Oracle)
Oct 10, 2007I 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.....
Server Does Not Allow Remote Connections
Jul 12, 2007Hi 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 RelatedSQL 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
SQL Server Does Not Allow Remote Connections
Apr 7, 2008I 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??
SQL Server Does Not Allow Remote Connections
Sep 21, 2006I 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
SQL Server Does Not Allow Remote Connections. (
Dec 8, 2006Hi,
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
Ql Server Does Not Allow Remote Connections
Jul 2, 2007hi,
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?
SQL Server Does Not Allow Remote Connections
Dec 18, 2007I'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.