Solved

SQL Management Studio connects to network sql server fine, yet apllication does not with same settings

Posted on 2008-06-16
7
276 Views
Last Modified: 2008-06-18
I have SQL Server 2005 Installed on a test Windows XP Pro SP2 (with firewall ports opened, Named Pipess / TCP enabled). I have SQL Management Studio 2005 and Visual Studio 2005 installed on my pc. I am writing an application that connects to the database in Visual Basic .Net.

I am using the following code to connect to the sql database server:

"Data Source=SQLTEST\SQLTEST;Initial Catalog=CDH;Persist Security Info=True;User ID=XXXXX;Password=XXXXX"

The UserID and Password are correct. I can login, using these same credentials in management studio. However, I get the error when I try to access the SQL DB from my application:

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

I can also ping the pc with the SQL Database just fine (just to further assure it's not a generic network issue).

On top of all this, this code/application worked just fine before we switched from a workgroup to a domain.

So, what am I missing?
Public Function Open() As Boolean
        Try
            If Not Me.ConnPath = String.Empty Then
                'If Me.Conn.State.Closed Then 'Create connection if non-existant
                Conn = New SqlConnection(ConnPath)
                'End If
                Conn.Open()
                'Successful
                Return True
            Else
                MessageBox.Show("The connection could not be opened because the application did not set the SQL connection path before trying to open a connection to the SQL Database.", "SQL Connection Failed", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return False
            End If
        Catch ex As Exception
            MessageBox.Show("Unable to open a connection to the SQL database." & vbCrLf & "Detail:" & vbCrLf & ex.ToString, "SQL Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            'Failed
            Return False
        End Try
    End Function
 
 
 
'Where ConnPath = "Data Source=SQLTEST\SQLTEST;Initial Catalog=CDH;Persist Security Info=True;User ID=XXXXX;Password=XXXXX"

Open in new window

0
Comment
Question by:DustinSpears
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21800696
possibly the datasource...

in sql ser management console right click on the server, then go to properties, then down towards bottom left and click on connection properties. Computer name and server name is what you are looking for...  sqltest as computer name is interesting, and probably is the bit that's wrong. You could also try localhost.

Also, while you have properties up there, check login - make it mixed Windows and SQL - or just SQL and make sure you connect via a "known" user - though, it does sound like the datasource is the issue...

0
 

Author Comment

by:DustinSpears
ID: 21802055
I agree that is what "sounds" right, but according to connection properties computer name is SQLTEST, Instance Name is SQLTEST and Server Name is SQLTEST\SQLTEST. Not to mention when logging in to sql management and connecting to this server, SQLTEST\SQLTEST is the server name I use, and I also use the exact same login. This works fine when connecting through management studio, yet connection fails from my application. I've even put the app as an exception in windows firewall along with ports 1433, 1108, 1109 (the latter 2 showed up in the log). The server has those ports open as well and the sqlbrowser.exe in exceptions. What I don't get is how from the pc, my app fails but management studio connects?

Thanks for trying.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21802389
Have you tried it as simply SQLTEST ?  Also have you tried nominating a Provider (yes, should not be needed) ? something like : Provider=SQLOLEDB.1;

have you got northwinds demo database installed ? try this...

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;"Initial Catalog=northwind")
nwindConn.Open()


0
Technology Partners: 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:DustinSpears
ID: 21803332
northwinds is a demo db from SQL2000 isn't it? this is sql 2005. You'll have to explain to me the provider part in code, but I know the datasource is not localhost as the sql server is located on another computer on the same domain, while the application is on mine.

Also, I have tried it just as SQLTEST, that doesnt work either (which I don't think it should, but yea I was out of options).
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 21803841
yes northwinds is the old demo, it is also available for 2005 - old habits...

I thought you were running it all on your PC... Gotta be a connection problem from Windows security... Will think on this a bit more...
0
 

Author Comment

by:DustinSpears
ID: 21813257
I basically scrapped the idea of using the Windows XP machine as a test SQL server. Using an old server to test off of. I believe it had something to do with domain wide restrictions that were applied by default form the domain server to the client pc (just as windows firewall is required on all client pc's by the domain). Thanks for the help, I'll just split the points up =D
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21813363
Bound to be domain restrictions / security at the windows level, good idea using an old server...  Thanks for the points...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

630 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