Solved

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

Posted on 2008-06-16
7
270 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
  • 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now