SQL 2012 :: Could Not Connect To Server Using Alias
Feb 17, 2015
I had two servers, in which SQL Servers are installed. I created a alias for server
Server 1 : test1.sqlcloud.com (Alias name with port no other than 1433)
Server 2 : test2.sqlcloud.com (Alias name with port no other than 1433)
I can connect to the servers using the port. for ex server1 to server2 and viceversa using "test2.sqlcloud.com,port". But I could not connect to the server by only specifying the alias name ("test2.sqlcloud.com"). When I am connecting I am getting the following error.
"TITLE: Connect to Server
------------------------------
Cannot connect to test2.sqlcloud.com.
------------------------------
ADDITIONAL INFORMATION:
Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21020; handshake=0; (Microsoft SQL Server, Error: -2)
View 1 Replies
ADVERTISEMENT
Jul 23, 2014
I am trying to setup a client alias to connect to a named instance on another server.
TITLE: Connect to Server
------------------------------
Cannot connect to fred.
------------------------------
ADDITIONAL INFORMATION:
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: 53)
[URL]
The network path was not found
------------------------------
BUTTONS:
OK
------------------------------
I have set up the alias on the server and as expected it works. On the client side I have used the SQL Server Client Network Utility to configure the client side alias but no luck
I am using TCP/IP with a specific port ..
By the way the SQL is SQL Server 2012 Ent and the O/S is Windows 2012 R2 minshell
View 9 Replies
View Related
Apr 25, 2001
I understand there is a way for a user to connect to a SQL Server
using an ALIAS ( i.e. I am connected with one instance using the
name DUBAY and now I want to make another connection with my
ALias - George). I'd appreciate anyone telling me how to do this.
Thanks in advance.
View 1 Replies
View Related
Jul 14, 2015
I know how to create an alias for a SQL server instance, so instead of MyServerMyInstanceName, I can type MyInstanceName into my SSMS connection box. But SSAS does not allow me to connect with the alias.
I've looked all over and found plenty of documentation that SQL 2005 and 2008 doesn't allow the use of instance aliases for SSAS. I've even found notes from Microsoft saying "we'll fix this in a future edition." I have not found any indications or notes as to whether or not this has been fixed.
Can aliases be created for 2012 SSAS? Or is this still not available?
View 0 Replies
View Related
Sep 21, 2015
I have some stored procedures that do updates using table aliases, like this:
UPDATE TableAlias
SET ColVal = 1
FROM RealTable AS TableAlias
WHERE TableAlias.ColVal <> 1
This allows me to include joins and stuff in the update (not shown) and make it all more readable for me.
It all works fine, and the SSMS parser says it's fine. But I also have another script which looks at sys.sql_expression_dependencies and sys.objects to find stored procedures with invalid object references (see below), and it's understandably saying that all of the above type stored procedures have invalid references.
SELECT
OBJECT_NAME(DEP.referencing_id) AS referencing_name,
DEP.referenced_entity_name
FROM sys.sql_expression_dependencies AS DEP
WHERE
-- Only validate local references:
(
DEP.referenced_database_name = DB_NAME()
[Code] ....
So I have a couple questions.
1. Is the UPDATE syntax I'm using kosher?
2. Can you recommend any updates to my stored procedure validation script that will better accommodate table aliases like mine?
View 5 Replies
View Related
May 27, 2014
As a database developer, I have so many databases that I "own" scattered across various servers that it''s getting difficult to remember where all of my databases reside. It doesn't work that the DBAs have taken to some very hard to remember server naming conventions.
I was going to create aliases via the configuration manager, but it turns out the DBAs overwrite my entries each night with THEIR aliases and they won't add any for my use.
So I decided to simply add some records to my host file so that instead of having to connect to "SERVER-AD_DADF-DAFDASS" I can just use "CustomerA".
This solution seems to work until I tried to access a linked server. If I connect to the main server via it's actual name, I can hit the remote/linked server with no issue. However, if I connect to the main server using it's alias, connection to the remote/linked server fails with: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
The linked server is set up to use current security context and I'm not logging into the alias any differently that I do when using the actual server name.
View 9 Replies
View Related
Oct 6, 2014
I have a bit of trouble getting values into one alias field, my code is below. I am trying to get values into the alias extension, Agent_ID is sometimes null, and so is agent_id2, however sometimes they both have values in them, and then only one of the values is every returned. When in the example below only Agent_ID (11111) is ever returned by I want both of them returned.
Agent_ID Agent_ID2
11111 22222
<code>
SELECT DISTINCT
CASE WHEN [AGENT_ID] is not null then AGENT_ID
when agent_id2 is not null then agent_id2 end as extension
FROM [AA_Helper].[dbo].[tblEmpData]
</code>
View 9 Replies
View Related
May 6, 2015
I have very unusual question. I have seen a query that looks something like this:
SELECT A, B, C FROM 'LDAP://abc.def.com'
What exactly does this mean? Could such a query be executed in the SQL Server Management Studio? Where would such a table/database be actually located?
View 3 Replies
View Related
Sep 1, 2015
Enabled DAC and when connecting, I get a "Failed to connect to server ADMIN:SERVERINSTANCE"; however query window shows I'm connected on ADMIN:SERVERINSTANCE and checking tcp_endpoints + dm_exec_sessions, I'm definitely on the DAC.
View 1 Replies
View Related
Sep 15, 2015
Every time I start SSMS 2012 the "Connect to Server" box is partially obscured in that the top of it, the move bar can't be accessed. I've manually moved it using "alt+space" then "M" followed by the arrow keys, but it's getting annoying. Â Is there any way to change the initial start position of this box?Â
View 6 Replies
View Related
Oct 17, 2014
I designed the AlwaysOn wrong, but every time we fail over from primary server to another server, my applications cannot connect because the sql logins cannot connect to their default databases. Once I run the command to link the login with the user in the default database then the users are able to connect. Did I do something wrong when designing AlwaysOn?
View 9 Replies
View Related
Nov 24, 2015
We will be moving 2 different databases (SS2005 & SS2008) to a new SS2014 SQL Server.  Currently our codes looks something like Server1DBInstance1... & Server2DBInstance2... Is it possible to move the objects from these 2 instances to Server3DBInstance3 and then use an alias to reference the objects?  Or does Server3 need to have DBInstance1 & DBIstance2?  Basically, is the alias just for the database or for the instance too?  Can I create an alias "Server1DBInstance1' on Server3DBInstance3 and assign objects to that alias?
View 16 Replies
View Related
Jun 22, 2015
I am unable to connect to reporting server , services are running fine .Checked in logs and found
ERROR: Error initializing configuration from the database: Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
How to resolve this ..
View 8 Replies
View Related
Sep 29, 2014
We are having 2012 Enterprise Edition,If I installed the Express Edition in other Client system is it possible to connect the Server system by using Express Edition....And Can we Connect the other server editions like Enterprise,Standard..etc by Express edition...
View 2 Replies
View Related
Sep 21, 2015
I have an SQL Server 2012 with SSIS installed on Windows Server 2008 R2 Core. I'd like to connect remotley to SSIS but I receive the following error. 'The RPC server is unavailable.'
View 2 Replies
View Related
Sep 21, 2015
The following error occurred when trying to connect to 2012/2014 SSIS Server using SSMS remotely. Local connection works fine.Using the info from below link does not resolve the problem. Â Permissions are granted through DCOM. If this cannot be resolved, packages will have to stored on filesystem instead.
URL....Connecting to the Integration Services service on the computer "" failed with the following error: "Class not registered".
This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.
View 6 Replies
View Related
Mar 20, 2014
I have a 3 node cluster on which I have installed SSAS as it's own insntance. I have created this as a named instance and can connect to it by serverinstance if I'm on the server itself. However from my desktop I get the error saying instance was not found on server name.
I have defined an alternate port and setup firewall rules and can connect via server:port but not serverinstance. Prior to making this change SSAS was running on default port of 2383 and I could connect just by servername.
I have read many articles for previous versions saying that clustered SSAS will always use 2383 and that you must connect just using servername. However and this is were it gets strange. I have a 2 node UAT cluster with SSAS setup exactly the same way I've described above and I can connect from my desktop as serverinstance.
Should I be able to connect as serverinstances for a named clustered instance in 2012 ?
View 4 Replies
View Related
May 13, 2004
how to add a alias to a server?
I have gone through BOL where it says it can be done thru right click agent -->> connections and configure alias.
i want to know if i configure a alias to a server from a client is it valid for other connections also?
meaning there are two database servers A and B
i add alias to server A by using client network utilities on server B,
then is this alias available if i try to connect to the server A thro some other server c?
hope this is not that confusing as it sounds. ;)
View 2 Replies
View Related
Mar 17, 2008
Is there any way to refer to an Alias in a select statement? For example, if I create an expression that sums to fields together and give it the alias TOTAL, and then in another field I want to create and other expresssion that multiplies a field by TOTAL, what is the syntax?
In MS Access the Alias name is surrounded by brackets, but I can't seem to find a way to do this with SQL Server.
Thanks
View 1 Replies
View Related
Dec 24, 2007
We have a SQL 2000 installation on a server called SATURN that has been around for a while. We've installed a new SQL 2005 installation on a server called DEIMOS. Our intention is to move all the databases that are currently on SATURN, to DEIMOS.
I've just been looking at the config settings, and it's suddenly occurred to me that the ability to set Server Aliases might save me quite a bit of work. What I'm thinking is setting a new alias on DEIMOS called SATURN. Then I won't need to go round the various applications changing the connection properties.
So:
Does this sound reasonable?
I intend uninstalling SQL 2000 from SATURN, but the server itself will stay - is that ok?
What will happen if SQL comes alive on the server SATURN while the alias is in place - effectively there will be 2 servers responding to SATURN. I ask this because my intention is to shut down all the SQL services on SATURN, set up the alias, and once I'm happy with the testing, then uninstall. Just wondering what will happen if for some reason the services were restarted before I finish.
Is there anything else I need to consider?Regards,
Tim
View 6 Replies
View Related
Dec 1, 2007
Today I found a problem with alias name during retrieving values from database table with alias name. My query was like as following:
========================================================SelectEmpID,Case EmpNameOrEmpFirstLastNameWhen ‘EN’ ThenEmpNameElse--EmpLast + “, � + EmpFirst
EmpLast + ', ' + EmpFirstEnd As EmpName,CityFrom tblEmployeeWhere EmpName LIKE ‘N%’=======================================================
When I execute this query I get all the record which contains the N% in EmpName as well as from FirstName. As you know that should not because I give alias name EmpName to EmpLast + ', ' + EmpFirst. But here the problem is we cannot use the alias name in Where condition.
I resolve this problem by using #temp table like the following:=======================================================SelectEmpID,Case EmpNameOrEmpFirstLastNameWhen ‘EN’ ThenEmpNameElse--EmpLast + “, � + EmpFirst
EmpLast + ', ' + EmpFirstEnd As EmpName,CityInto #tmpEmployeeFrom tblEmployee
Select * from #tmpEmployee where EmpName LIKE ‘N%’=======================================================
It really very simple, isn’t it? You can use the reference link:Reference: http://www.thescripts.com/forum/thread82710.html
So friends enjoy, we are the Sql Master:)Regards,Vijay Modi
Reference: http://vijaymodi.wordpress.com/2007/05/23/sql-server-problem-using-alias-name-in-where-condition/
View 2 Replies
View Related
Apr 3, 2002
I have a SQL Server 6.5 db on a server, we need to migrate SQL Server to another server.
We have about 400 users connected to an OBCD driver pointing to the original server and
would like to connect them to the new server.
How can I do this without changing all the users OBCD settings?
Is there a function to alias the new server?
View 1 Replies
View Related
Jan 17, 2008
I am moving my Sql Server installation to a larger machine. In doing so, I don't want to change everyone's connection settings. Does anyone see any issues with using an DNS alias to connect to the new server?
Using the same name is not an option per the windows admin group that is provisioning the machine.
Thanks,
Andy
View 1 Replies
View Related
Oct 6, 2006
Hi,
In DTS 2000 when you created a connection you could use a server alias (as created in the network client utility) as the servername. When I try to use a server alias in connection mgr(I am using Native Client) it fails to connect with the standard error: "Test connection failed because of an error in initializing provider. Login timeout expired. etc etc...
Has anyone had success using a server alias? (Yes i set up the alias using sql configuration mgr)
The problem I am trying to solve is I have dev/test/production environments that I want to move this package to and I don't want to have to change any server name properties within the package when moving to the different environments.
Thanks
View 4 Replies
View Related
Jun 3, 2015
so you have two place to configure alias, in SQL server configuration manager under aliases tab on and on the cliconfg.exe tool on a different machine. If I want another server to see this sql server by the alias name I will need to configure the alias on the cliconfg tool on the client machine. It looks like as long as I have the alias configured in the cliconfg on the client machine, I don't need the alias to exist on the config manager, the client can still see SQLserver using just the alias on cliconfg...if so, I wondering under what situation I would really need the alias on the configuration manager?
View 7 Replies
View Related
Oct 30, 2006
Hello,
I would like create/modify/update the alias SQL server name on the SQL 2005 version.
I had a database mirroring on one base, and when the primary server fail, i would like my secondary alias become as the primary. I need this because of the application whom use my SQL server.
I tried with
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
But this way doesn't work...
Anyone got an idea to change alias SQL server in transact?
Thanks for your help!!!
View 1 Replies
View Related
Apr 4, 2008
Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
Thanks
Sean
View 2 Replies
View Related
Apr 27, 2015
Is there a way to generate report of how many server alias exist on a server. I try to extract using xp_cmdshell but unsuccessful
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerClientConnectTo
View 2 Replies
View Related
Jul 30, 2015
I use DNS alias to access my database server:
server name is -> SRV100
DNS Alias is -> SQLPROD
I've noticed that, using Windows authentication, if I connect to the server using its server name, the DB Engine uses Kerberos authentication scheme (as it is supposed to do) but if I use Kerberos authentication, I see that the DB Engine uses the NTLM authentication
scheme
select client_net_address,auth_scheme from sys.dm_exec_connectionsÂ
I need to use DNS alias to connect to my server and I want to use Kerberso auth scheme.
View 4 Replies
View Related
Oct 2, 2015
basically, we have two databases, one with fix name, eg. DB1 in all environments, and another is named with prefix based on environment, eg. DB2_DEV, DB2_TEST, that is generated by some managed application.Â
Then we have queries or view/SPs residing in the first database, eg. DB1 that access database resources from the second database, DB2_***. Both databases are residing in the same SQL instance.
Currently, we have hardcoded the database name in the query but would prefer not to manually rename or write other scripts to update the query with the correct database name when deploying to other environments.
For example:
SELECT * FROM DB2_DEV.dbo.vAccounts
When deploy to TEST, we need to update the query to:
SELECT * FROM DB2_TEST.dbo.vAccounts
So, I looked at Synonyms but it seems it can only create 'alias' for tables or views, and not database.
Hence, last thought is to create self linked server, so I can write my query like below and setup the linked server accordingly.
SELECT * FROM SRCDB.dbo.vAccounts
Any consideration to think of, eg. performance, security, etc?
View 8 Replies
View Related
Jul 5, 2014
I am struggling to set a SQL Server Express for remote access...
Since it is the only instance on this server, I have set it up to use port 1433 by amending the IPALL entry of the Server Network Configuration, TCP/IP Properties.
I have also disabled "Shared Memory" and "Named Pipes" to leave only TCP/IP enabled as a server network config.
I have stopped the Browser Service because I think I don't need it if I use port 1433.
From the server itself, I can connect to my SQL Server through "localhostSQLEXPRESS" or "servernameSQLEXPRESS" but not from "10.xxx.xxx.xxxSQLEXPRESS" or "127.0.0.1SQLEXPRESS"
I suppose until I can access my server from itself using the IPAddress, there is no chance connecting remotely...
View 5 Replies
View Related
Aug 17, 2014
I use SQL Server 2012 Management Studio, SQL Server Data Tools and Visual Studio 2012 to deploy a data mining project.
I get the following error message: A connection can not be made. Make sure the server is running.
I am able to pick up the database using the details below:
Machine Name: NIRMALK01-PC
SQL Server: SQLEXPRESS
SQL EXPRESS: Running
SQL BROWSER: Running
SQL Agent: DISABLED
I can not find Analysis Services in services.msc
This is the error: Error 1
The project could not be deployed to the 'nirmalk01-pcsqlexpress' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.
View 2 Replies
View Related
Feb 24, 2015
We have a monitor tool to monitor our SQL servers.The vendor has a script to grant permission to application account to SQL server so that they can do the monitoring.
I don't want to grant system admin role, but the script they have is very specific, the minimum is:
must be a member of db_datareader role on the msdb database
must have view server state permissions
view any definition
connection permission to master database
execute permission on the xp_readerrorlog stored procedure
connect permission to the msdb database
must be member of db_Datareader role in the msdb datab ase
connect permission to all databases.
The script given by vendor is as follows in the attachment.I don't like the last execute statement for if a new database is created, we have to remember manually add the monitoring acccount to that database.
I know in SQL 2014 it has a new feature of connect to any database, but unfortunately we are using SQL 2012 and 2008.
script to connect to any database but no need to add that each time we creates a new database.
View 5 Replies
View Related
