[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server - Login timeout expired error but only on one server

Posted on 2011-05-03
35
Medium Priority
?
2,800 Views
Last Modified: 2013-11-10
I am trying setup my app on my prod server the way I did on my dev server.

I created an SSIS package on my devserver by using sql server management studio, opening a db, selecting import then using the wizard.

I did the exact same thing on my prod server, using the exact same settings and when I do, I get a Login timeout expired error.  I can ping the server I am trying to import from both prod and dev.

Since I can ping both, and the settings are the same, I think that the issue is on my prod server.

I have no idea how to fix this.
0
Comment
Question by:jackjohnson44
  • 13
  • 8
  • 6
  • +2
34 Comments
 
LVL 3

Expert Comment

by:ajmehta
ID: 35517629
might be timing out due to security restrictions.

Try to use windows (integrated) authentication, or to make sure that your username has permission to restore the database (or are you really importing records - not restoring)?
0
 

Author Comment

by:jackjohnson44
ID: 35517681
I am importing into a database.  I just created the DB so I should have access to it. I believe that the issue is with the server not being able to connect.  Here is the full error.

 
TITLE: SQL Server Import and Export Wizard
------------------------------
The operation could not be completed.
------------------------------
ADDITIONAL INFORMATION:
Login timeout expired A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server [53].  (Microsoft SQL Server Native Client 10.0)

Open in new window

0
 
LVL 3

Expert Comment

by:ajmehta
ID: 35517708
sounds like your path to the SQL instance is wrong.

for example, are you pointing to .\SQLExpress (or similar).

You cannot just use 'database=model' in some cases, you also have to specify the actual server instance like "instance=.\SQLExpress'
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jackjohnson44
ID: 35517761
I don't think you understand the question.  I can't connect via SQL Server Management studio to another server via the import wizard on one server.  I can on a different server using the exact same settings.
0
 
LVL 3

Expert Comment

by:ajmehta
ID: 35517772
oh, yeah i didn't realize that's what you're doing.  Can you disable the firewall temporarily and let me know if you have the same effect?
0
 

Author Comment

by:jackjohnson44
ID: 35517779
The firewall says that it is disabled.  I can ping the other server, but I can't connect via SQL.
0
 
LVL 3

Expert Comment

by:ajmehta
ID: 35517784
Can you try to login using the "sa" account of the remote SQL instance?
0
 

Author Comment

by:jackjohnson44
ID: 35517787
No.  I am using the exact same login on two different servers side by side.  The issue is not the sql login, it has something to do with a network connection issue.  
0
 
LVL 3

Expert Comment

by:ajmehta
ID: 35517804
Just to confirm, your firewall on the client (not sql server) is disabled while you are trying this?
0
 
LVL 3

Expert Comment

by:ajmehta
ID: 35517816
I still think your path to the server might be mal-formed... try to double check that you have it pointed to the *exact* same string on both machines, like "mysqlserver\sql2005"
0
 

Author Comment

by:jackjohnson44
ID: 35517819
Thanks, this is getting really long.  I am going to award you points and ask another question.  Please give someone else a chance to respond.
Thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35517832
On the remote SQL server you are trying to connect to, ensure that you have enabled remote connections over TCPIP.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35517841
@jackjohnson44: for future reference, you should use Request Attention link to ask a Moderator to attract more Experts versus awarding an unanswered thread to open another question.  You can do so now and ask a Moderator to unaccept this post since per your comment it is unanswered.
0
 

Author Comment

by:jackjohnson44
ID: 35517854
Thanks, but I already posted.  In my experience once a question gets too long, no one answers.  This is extremely URGENT, and I really, really, really need assistance.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35517895
@jackjohnson44:

Now that the question has been re-opened, please try to see if the remote server holding DB1 has been enabled for remote connections.

Please post back once you have.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35517906
In the interim, I will post this in the SQL notify thread and see if other Experts have thoughts that have been missed here so far.  As you will find reading through the error message, the issue is likely though that you have not enabled remote connections which in newer versions of SQL is disabled by default to be more secure by default.  

In your scenario one, I suspect you are importing from DB1 which happens to be on the same server; however, in scenario two are connecting to server from scenario one to import from DB1.  Am I correct?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 35543005
The thing that I do is always disablle named pipes. NP was designed for small fast networks and still exist just because enough programs still exist with them in mind.

Now the next thing to be sure of is the SQL Browser Service is started. And has been restarted since the instance (default or \InstanceName) was brought online.

The next thing is to make sure the SIDs are consistent between both DB servers are consistent. Look in the Sys.logins in the master DB and the sys.users tables in the databases. If they aren't then use the How to transfer logins and passwords between instances of SQL Server article to get them consistent.

Then after that, get back to us and we can see what we can do.
0
 

Author Comment

by:jackjohnson44
ID: 35689114
Thanks guys.  I really don't think that I am explaining well.

There is one source database DB1 which I am trying to connect to which is located someplace else.

On rackspace I have a server DEV1 and PROD1.  They are on the same network as each other and not on the same network as DB1.

DEV1 can import from DB1.
PROD1 can not import from DB1.

All SQL settings are identical.  I open two remote desktop windows and enter the server name, username, password, and database name in the import wizard by cutting and pasting.

DB1 definitely is running, the login name, server name, password, etc all work since I can import from DEV1.

The issue is with PROD1.
0
 

Author Comment

by:jackjohnson44
ID: 35689118
I can also ping DB1 from both DEV1 and PROD1
0
 

Author Comment

by:jackjohnson44
ID: 35689181
I looked at the link someone posted.  The firewall on PROD1 is not turned on (DB1 already works since I can connect via the other server).  There is no instance name there is just a server name.
0
 
LVL 11

Expert Comment

by:Lara F
ID: 35689804
Ping only confirms that you PROD1 box can resolve name. I can ping www.google.com but it does not mean I can connect to google's DB servers.

You tell that your DB1's server is somewhere else. Is your DB1 server behind firewall? Firewall can be configured to allow only specific IP addresses.
Can you connect to DB1 box via RD from prod1?
Check if you have VPN on your DEV1 server.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 35691117
Also try

telnet Servername 1433
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35692375
Ah, that is a different story, thanks for clarifying.  So you have 3 servers in the mix with 2 on the same network.  Are all three servers the same version of SQL.  

More importantly, is your production PROD1 SQL server at a different version than the DEV1 box?

It may be a SQL Native Client (driver) or SQL Management Studio version issue if you are dealing with different versions.  

I know you said you can ping the server just fine, but if you think this is an issue with a configuration problem on PROD1 for kicks can you try connecting via the IP address of DB1?

And just to not assume, the login and password are SQL Authentication (i.e., not Windows/Active Directory accounts), correct?
0
 

Author Comment

by:jackjohnson44
ID: 35692600
I have a new development, thanks for the comments.  I think that ultimately the issue is that the firewall is set to a specific ip address, even though someone is telling me that is not the issue.


I was incorrect with one of my statements above
DEV1 is a web front end, that I logged into and used SQL manager from
PROD1 is just a database server

I was able to successfully login to DEV1 and execute an SSIS package between the source DB and my PROD1 DB.

So:
I can import from DB1 into PROD1 if I do this from a third server using SQL Server Management studio.  If I try to do the exact same thing logging into PROD1 directly, I get the error above.

I guess this appears to be an issue with a direct connection between the two servers.

mwvisa1: all servers are running the same version of SQL.  This is actually a SharePoint environment and they all need the same version.

jim: telnet to that port didn't work


0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35692604
Try having the DEV1 information up and hand typing in the information on PROD1 also, just in case the copy and paste is bringing over some extraneous character.  Might be silly, but at this point might be worth the try in the troubleshooting process.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 35692636
Crossed posts.  Okay, sounds like the web server is either in a DMZ which allows it to access the other network and namely DB1 or as you are being told the Firewall rules are specifically allowing tunnel between networks to the DEV1 box.  Glad you found the issue.  If you can't change the network, you can simply use DEV1 as the execution point to do ETL between DB1 and PROD1.
0
 

Author Comment

by:jackjohnson44
ID: 35692678
If it isn't a firewall issue could it have something to do with when a machine is used as a web front end, some additional protocols are setup?  I was told that it has iis because of reporting services, but I wonder if SharePoint opens something else up.
Make sense?
0
 
LVL 11

Assisted Solution

by:Lara F
Lara F earned 1000 total points
ID: 35693176
I don't think it is difference in settings between you prod1 and dev1 servers, unless your login  on dev1 configured to automatically start VPN connection to DB1 network
Most likely this is different settings on your DB1 server access permission, which is usually done on firewall level.

Try to open connection to DB1 server from prod1 servre outside of sql management, just trough udl file.
0
 
LVL 11

Expert Comment

by:Lara F
ID: 35693227
DO you have any tech support at place that has your DB1 server? network admin should be able to look at firewall logs and tell you if there are any denial on it.
0
 

Author Comment

by:jackjohnson44
ID: 35693272
Someone just sent me an update saying that the changes were applied.  I believe that they had to specify the ip address.

I was able to connect with the PROD1 and went through the steps to create the package.  I tried saving it to the db and it said that it was already there and wanted to know if I wanted to replace it.  Since the package lives on the database, does it get run for the db too?

When I tried to click finish, it said that new rows would append to my tables.  I don't want this.  I want to overwrite or update.  Is this going to be a problem?  If I ran the original package would the same thing happen?  I need this to update nightly not append.  Sorry for the follow up question, but I am not sure if creating this second package will hurt me.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35693579
Sounds like the firewall between the two networks had a rule by IP address in it.  That makes sense in most large environments as the SQL ports aren't generally open across corporate firewalls unless necessary as in this case.

As for the SSIS package and appending to tables, you should have an option to delete the destination table's rows and repopulate from import.  If this is really what you are doing, it may be better to setup DB1 as a linked server and have a procedure that at night:

(1) Performs a TRUNCATE on table
(2) INSERT into table from DB1

or

(1) INSERT new rows
(2) UPDATE existing from DB1

You can accomplish this by having your SSIS package dump to a temporary table and then perform standard UPDATE / INSERT SQL statements within the package to accomplish the merge of data.  On SQL 2008, you can look into MERGE (http://msdn.microsoft.com/en-us/library/bb510625.aspx).
0
 
LVL 11

Expert Comment

by:Lara F
ID: 35693801
In import wizard you have option "append" or "overwrite" existing table choose what you need.
One other option to check (actiually "uncheck") is to use "do not import existing users"
You are better with package - running queries through linked server usually slower
0
 

Author Comment

by:jackjohnson44
ID: 35700016
mwvisa, and trof - I want to close this question for the others.  I have some questions about the SSIS (why can't I find the zone) here.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27020678.html
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question