Solved

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

Posted on 2008-06-16
7
273 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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