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

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

DustinSpearsAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
DustinSpearsAuthor Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Mark WillsTopic AdvisorCommented:
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
 
DustinSpearsAuthor Commented:
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
 
DustinSpearsAuthor Commented:
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
 
Mark WillsTopic AdvisorCommented:
Bound to be domain restrictions / security at the windows level, good idea using an old server...  Thanks for the points...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.