Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-16
7
Medium Priority
?
277 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 2000 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

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!

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

715 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