July 30, 2008 at 12:34 pm
Hello folks -
I am trying to connect to the default instance of SQL2K5 on a remote server with Crystal Reports. I can connect to my local computer with no problems. Both the server and my local system are running SQL2K5 Express. The server is marked for remote connections and the number of connections is unlimited.
The set up information is:
Using OLE DB (ADO) and selecting Microsoft OLE DB Provider for SQL Server
Connection Information:
Server - HMM123\SQLEXPRESS
Database - cSupport
Integrated Security is checked
The error message is:
Failed to open the connection.
Details: ADO Error Code 0x80004005
Source Microsoft OLE DB Provider for SQL Server
Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access is denied.
SQL State: 08001
Native Error: 17 [Database Vendor Code: 17 ]
Any help is appreciated.
Thanks,
July 30, 2008 at 12:40 pm
Usually when this happens it is because remote connections are not enabled in Express, I know you said it was on, but are BOTH tcp AND named pipes enabled? Can you connect to this remote server using SSMS or Acccess or some other application other than Crystal?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 1:24 pm
Hi Jack and thanks for responding so quickly. This is a Helpdesk application and we can connect via a web URL. I can not connect (so far) with SSMS. I heard somewhere that the Express version has some major limitations. The Helpdesk software folks are going to SQL Reporting Services and are phasing out Crystal.
Where do I check in SQL2K5 for the settings for Named Pipes and TCP/IP? I have checked the properties for the instance and did not see any option there.
Do I need an ODBC connection for Crystal? I did not need one for SQL 2000. (we did a hardware upgrade) All the databases are fine and can be accessed via the helpdesk application.
Thanks again.
July 30, 2008 at 1:30 pm
I don't know anything about Crystal Reports so as far as needing an ODBC connection, from the SQL Server side I don't think so. On the SQL Server you check the settings using SQL Server Surface Area Configuration which is under Start -> Programs -> Microsoft SQL Server 2005 by default.
Are you sure that your Windows account has rights to the database?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 1:40 pm
SQL is set for both named pipes and TCP/IP for local and remote connections. I have tried running under my user account and sys admin accounts. I am DBO in both.
I'm sure it's something simple, I just don't have that much experience with 2K5. (Everything here has been SQL 2000 due to legacy with old applications.)
Once I get this going, I'd like to manage the servers from my local box like I do with Enterprise Manager.
Can you think of anything else I could try?
July 30, 2008 at 2:16 pm
Is there only 1 instance of SQL Express running on the server? Did the default port get changed?
If your Windows user is a login to the box and a user of the database then you should be able to use SSMS to connect and manage the SQL Server(s) just like using EM in 2000. Do you have a SQL Server user that you could attempt to login to the SQL Server with?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 2:21 pm
Yes, only one instance - the default and no, the port has not been changed.
July 30, 2008 at 3:22 pm
Sorry, I missed the second part of your reply - If your Windows user is a login to the box and a user of the database then you should be able to use SSMS to connect and manage the SQL Server(s) just like using EM in 2000. Do you have a SQL Server user that you could attempt to login to the SQL Server with?
I am a member of the domain admins and can log in to the server both locally and remotely. I agree with you that I should be able to use SSMS to connect and manage just like EM in SQL 2000.
I even tried to create a SQL account with the same results. It's like the server or SQL 2K5 doesn't exist - it can't be found by either SSMS or Crystal. I had a similar problem with my system locally but it turned out that I did not use the fully qualified name: HMM-123\SQLEXPRESS.
July 30, 2008 at 3:26 pm
Is the SQL Browser service running on the server? If not, try starting it and see if you can connect.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 30, 2008 at 3:28 pm
Are you sure that SQL Express was installed as a named instance and not as the default instance? With 2005 you can install SQL Express as the default instance. So you could try connecting to HMM123 (this is what you have in your first post is it supposed to have a '-' before the 123 like in your last post?).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 3:38 pm
Yes. The HMM-123 reflects workstations whereas HMM123 (no -) represents servers. So, HMM-123\SQLEXPRESS is a workstation and HMM123\SQLEXPRESS is a server. Sorry for the confusion.
July 31, 2008 at 6:35 am
Eureka! Jeffery, your suggestion for turning on the SQL Browser service took care of the problem. I connected so fast it was unbelievable! Crystal Reports works fine and now I can manage with SSMS.
Thanks Jeffery, you made my day. And, thanks also to Jack. I learned a couple of things from your suggestions, and I'll bet a few folks out there did too.
Finally, thanks to this site! Wonderful and helpful information all around.!
July 31, 2008 at 10:07 am
Glad I could help and you are up and running. The SQL Browser is required when you have named instances to identify the dynamic port that instance is running on.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply