Note: but even the connection string "serverBserverA,2040" also connects to Instance "serverB", when i try to connect from SSMS, In SSMS it shows the wrong instance name like, "serverBserverB", but it actually connects to Instance "serverA"
i.what is the reason behind this?
ii.Is there any way to avoid this connection, by performing server property changes, if any ?
I've two instances(Default, Named[dynamicsFINANCE]) running on SQL server 2014. However, when I try to connect to named instance say (dynamics FINANCE) 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.
connecting to a SQL Server Instance. I have a SQL server DB on a server having an Instance not running on the default port. Let's say ServerAInstanceA has the services exposed on Port 12345. When I'm trying to connect to ServerAInstanceA without specifying the port number, while I can connect from one client machine (ClientA), I am unable to connect from a different client machine (ClientB).On a side note, when I provide the port number, I can connect both from clients A and B. My question is, when I do not provide the port number, why is one client machine able to connect while the other isn't?
We have an internal sql 2000 server that acts as the database backend for our Internet presence. I believe that the sql server can be accessed because of a firewall "exception" (?) that allows the external web server to talk to the sql server using a specific IP address and port (namely that of the sql server.)
Now we need to install a sql 2005 instance on the same box which needs to act as an Internet backend. The sql browser service will resolve all client requests to the default or named instance. But I read that the sql browser service assigns a "dynamic" port to the Instance! If that is the case, it's unlikely that, unless the firewall has all ports open for outgoing traffic, that the sql instance will be able to communicate with the web server.
My conclusion:
Leave the default instance (sql 2000) port at its current value; assign the named instance an unused port; Make sure the sql browser is running on the box! Tell the firewall guys to make an exception for the new port.
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.
I have an existing 2012 default SQL Express. It's set up on a VPS managed by a third party. I have an administrator account on this 2012 Windows server. I'm not much of a sysadmin or a DBA but I get around. ;)
I've installed a new NAMED instance on this VPS and can not connect to it with client tools (SSMS). If I remote in, I can connect this way.
What steps might a seasoned DBA expect to make when getting a new named instance ready for the world.
Assign a port? Check the port?
Open the firewall for the port?
Will this new named instance listen on a different port than the previously installed SQL Express instance?
I just installed sql server 2014 on a second pc I will be using to vpn into our parent company's domain. When I use explorer to look at file system folders on servers in the parent domain on which I have permissions, I am prompted correctly for my windows creds in their domain which are different from the creds in my originating domain and I can see those files. But when I open ssms and try to connect using server name or ip address, I see two problems....
1) ssms is using my originating domain creds without giving me the opportunity to change them 2) whether I use a server name or ip address I get an error saying login from an untrusted domain cannot be used with windows auth 18452.
SQL2008R2...Have a SPID that started yesterday and contained a command to drop a server audit. This did not finish (after 3hrs) so I killed the SPID. Problem is it's still there (sp_who2) and I think it's now blocking access to viewing editing logins within SSMS as I'm getting "Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)" when double clicking a login.Do I need to restart the instance as killing the "alter server" transaction (SPID=62) is not working? Code to show blocking transactions returns this.
I recent installed a named instance of SSRS 2005 on a remote server. The server has a side by side install of SSRS 2000 and SSRS 2005, with the SSRS 2005 being a named instance. The server also has SSL and MS Sharepoint Portal Server installed.
When I try and connect to the SSRS 2005 instance with my client install of Mangement Studio, I get the error message:
"The underlying connection was closed: Could not establish a trust relationship for the SSL/TLS secure channel. (System.Web.Services)
Additional Information: The remote certificate is invalid according to the validation procedure. (System)."
Obviously, this is a SLL issue, but, not being a security guru, I have no clue on how to fix this. I do know the certificate is valid, not expired and properly working. Both installs of SSRS are set up using the SSL certificate. Also, I am not connecting to SSIS with Management Studio, as I heard there is a bug connecting to both SSRS and SSIS at the same time.
I have two queries yielding the same result that I wanted to compare for performance. I did enter both queries in one Mangement Studio query window and execute them as one batch with the actual query plan included.Query 1 took 8.2 seconds to complete and the query plan said that the cost was 21% of the batchQuery 2 took 2.3 seconds to complete and the query plan said that the cost was 79% of the batch.The queries were run on my local development machine. I was the only user. No other programs were running at the time of this test. The results are repeatable.I understand that the query with the lowest cost is not necessarily the fastest query. On the other hand, the difference is quite big. The query that has approx. 80% of the cost takes 20% of the time and the other way around. I have two questions:
Is such a discrepancy normal?Can conclusions be drawn from the cost distribution? For instance, does the query that takes 8.2 seconds but only costs 21% scale better?
I have installed a SQL Server 2005 Standard Edition instance with SSIS on Window Server 2003 x64. From my PC, I am able to connect to the Integration Services on the server using Windows Authentication but not with a local SQL login - and the option to change authentication methods is greyed out.
Is it possible to remotely connect to SSIS using a SQL login rather than a Windows login? If so, any ideas or references for configuring it as such?
Hello, I installed SQL 2005 Standard on the same server (2K3) with SQL 2000. My named instance I installed is coming up with "Version 8.00.2039" in the Server Management Studio and will not let me restore a database back-up I made on a 2005 development workstation (I'm assuming it's because it's trying to use the 2000 engine instead of the 2005). Is there a way I can force this instance to use the 2005 engine rather than the 2000?
I have three instances running on my sql 2008 r2 enterprise.I have taken full backup of one database from default instance.I login both the instances thru sa account.Now when I try to restore that backup on another instance it gives me the following error....
TITLE: Microsoft SQL Server Management Studio Restore failed for Server 'WRCP2F1CWS315TEJA'. (Microsoft.SqlServer.SmoExtended) System.Data.SqlClient.SqlError: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAfcms.mdf'. (Microsoft.SqlServer.Smo)
what can be done to restore bakup in another instance ...
I have installed a default installation of a named instance of a 2008 R2 server and I want to restore a server from the main system and application files. I have a bak file for the latest master and model database and the four application databases and transaction log files. If I restore the master database in single - user mode I will get an error when I start-up the SQL service as the other databases are not attached to the SQL instance.
Is it restore databases and then master/model? best way to restore a SQL server environment?
I recently installed SQL Server and Visual Studio. When I went into SQL Server, I could only connect to a SQL EXPRESS Database engine. I need to access the full Database. I tried disconnecting it, uninstalling/reinstalling. Now I cant connect to the SQL EXPRESS DB Object. There are no SQL Server services running.
I have SQL Server 2014 Enterprise Edition with a number of in-memory tables sitting in my database.When server is restarted it takes many hours to recover my database if there was data in these in-memory tables before shutdown.As a result, I need to clean up in-memory tables every time before server instance shutdown. This is really annoying and requires extra prescriptive actions for support team. Can I have DDL server/database level trigger to catch shutdown event and clean my data before instance goes down?
We have 3x instances of SQL Server 2012 installed on a single remote server - there's the default MSSQLSERVER instance, then INSTANCE01 and INSTANCE02. I can remotely connect to the default MSSQLSERVER instance through SSMS, but I cannot connect to either of the additional named instances (INSTANCE01 or INSTANCE02).
For example, if I try to connect to "sql.domain.com", I can successfully access the default instance on the remote server. If I try to connect to "sql.domain.comINSTANCE01", I get an error stating
"A network-related or instance-specific error occurred while establishing a connection to SQL Server".
However - if I try to connect to "sql.domain.comINSTANCE01, 49301" (where 49301 is the TCP Port for the TCP/IP Protocol for this SQL Server instance), I am able to successfully connect.
This leads me to think that there's a communication issue with the SQL Server Browser service running on the remote SQL Server and my workstation.
The following items have been verified:
SQL Server Browser is running on the remote SQL ServerWindows Firewall has been disabled on the SQL ServerTCP Ports 1433, 1434, 1954, and 49301 have been opened up on the remote destination's firewallUDP Port 1434 has been opened up on the remote destination's firewall.
I have TWO named SQL Server instances (on the same machine) and I need to know the port of each of them, how can I do that? Is it write to check the following:
Which one to take: "TCP Dynamic Ports" or "TCP Port"? and what is the difference between them anyways?
Can the two instances (or more) on the same machine use the same port?!
We are currently looking at consolidating 10 servers into one cluster server.
Some servers may be busier than others. Is there any reason to split them up and give the busy databases specific CPUs or is it always better to have them on one instance?
Since installing the client tools for SQL 08 R2 on my laptop, I am running into the following error when trying to open a step within a SQL job:
Microsoft SQL Server Management Studio
Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)
ADDITIONAL INFORMATION:
Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)
I get "there was an unknown error applying the filestream settings("0x800710dc ") configuring my sql clustered instance with configuration manager. Not clear for me what I have to set for "Windows shared name".. Is it just an aliases or do I have to give the name of a shared disk ??
I've a fresh installation of SQL Server 2014 Enterprise on Windows Server 2012R2. I've setup de Windows Server Failover Cluster and the validation test has been successfully passed. I use mountpoint so I've disk M: that is the host drive for mounted volume m:Isql2014A that is the base folder for mounted volume
into these 5 mountpoint I've placed the SQL Server files.
After the installation of the first FCI I've re-run the cluster validation wizard and again it has passed successfully. When I try to failover the instance from one node to the other I have 2 problem:
1) every time I do failover, some disks fail to come online for a while and then succeed :
Cluster resource 'CLD - IstA - TempDB Data' of type 'Physical Disk' in clustered role 'SQL Server (ISQL2014A)' failed. The error code was '0xaa' ('The requested resource is in use.').
2) sometimes the SQL Server service fails to start, in the error log I see that the master database is not accessible. The master database in into the m:isql2014abinbackup.
I am new to SQL Server 2012 clustering.I added a new instance to one of the two nodes.when I try to move it to the other node it fails.Do I need to install it on both?If so, what options do you install on the second node?
One server, a part of a 2 node always on cluster, had problems, I had to restart.
I'm getting this error for example now:
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. + 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.
Now, on the instance I have activated maximum servere memory, my question; Is it possible to adjust this setting without starting the service?
In our production we have a database by name MyDb.The application team wanted another database by the name MyDbOld which contains the data of Mydb 1 month old. So I created a database MyDbOld in other instance(test) and restored MyDbOld from the one month backup of MyDb in production server. In test instance I backed MyDbOld as MyDbOld.bak...
In the production instance I created a new DB by the name MyDbOld and restored the MyDbOld in the production from the backup of MyDbold.bak in the test instance. Now I have 2 databases in the production instance ie MyDb and 2)MyDbOld. However I find the logical names of both the databases to be the same although the database name and the physical file name to be different.My questions are-:
1) Does it in any manner affect the integrity of the 2 databases if the 2 databases are in the same instance and have the same logical name? 2) Would the dml or data retrieval operations in the 2 databases have any conflict in any manner what so ever? 3) Having a unique database name, and unique file names for the physical files for any database in an instance what is the purpose and significance of additionally having a logical name for a database? 4) Which I could restore a database from the backup of another database in the same instance and at the same time change the logical and physical name of the files to correspond to the new database.
I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream". OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?
Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.
What ports does SQLMail use to access MS Exchange Server?? We have SQL 7 running on a seperate non-trusted domain(within the same DMZ) from where Exchange Server exists. The only trust we want between the 2 domains is the SQLMail functionality for the DBA's and Administrators to use. I am aware that MS Exchange uses 110 port for POP3 and port 25 for SMTP. Does this apply to SQLMail as well? Thanks in advance for your support.
hi every on am searching for a file which contains information about the instance in my sql server and the ports that this instances are running on ,am sure that there is a file like this but i cant find it please if u know it answer me ,,, and thanks alot for ur time//
