-
Jul 21st, 2016, 09:15 AM
#1
Thread Starter
Lively Member
SQL Server 2012 connection error
Hi all,
I can add the SQL server to my 'Server Explorer' and 'SQL Server Object Explorer' very easily by entering the server name and selecting that the server uses 'Windows Authentication'.
However, when i try connecting to it through Visual Basic (Windows forms application) i get an error. Just to see if i can get a connection i use the following code: -
Code:
Imports System.Data.SqlClient
Public Class Form1
Private Sub btnConnect_Click(sender As Object, e As EventArgs) Handles btnConnect.Click
Try
Dim myConn As SqlConnection
myConn = New SqlConnection("Data Source=Solution;Initial Catalog=DataWarehouse;Integrated Security=True")
myConn.Open()
MsgBox("Connected Succesfully…")
Catch ex As Exception
MessageBox.Show(ex.Message, "Error")
End Try
End Sub
End Class
But i get the following error every time.
My IT Department cannot figure it out, and i have tried everything i can find online like:-
- Making sure "Allow remote connections to this server" is enabled
- Checking all the SQL services are running
- Make sure the Client Protocols are Enabled in SQL Server Configuration Manager
Now im all out of ideas.
Any suggestions on things to try would be great.
Thanks in advance
Chris
-
Jul 21st, 2016, 09:21 AM
#2
Re: SQL Server 2012 connection error
Are you able to see the tables connecting through SQL Server Management Studio?
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jul 21st, 2016, 09:34 AM
#3
Thread Starter
Lively Member
Re: SQL Server 2012 connection error
Originally Posted by kaliman79912
Are you able to see the tables connecting through SQL Server Management Studio?
Hi Kaliman,
Yes can see all Tables and Views fine through SQL Server Management Studio
-
Jul 21st, 2016, 09:37 AM
#4
Re: SQL Server 2012 connection error
a couple of notes.
* If you are using the same computer where the SQL server is located then there is no risk with the remote connections being accepted or not.
* The error message is specific to the name of the server, it is not finding any server with that name. You can omit the part for the initial catalog and the integrated security, if the server was ok then it would find it.
* Are you sure you are not using a named instance? if that is the case, the name of the server should be something like: SERVER\INSTANCE
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jul 21st, 2016, 09:42 AM
#5
Re: SQL Server 2012 connection error
-
Jul 21st, 2016, 09:45 AM
#6
Re: SQL Server 2012 connection error
If you can get into SSMS - then right-click the SERVER in the OBJECT EXPLORER and you will see a clickable link in the lower-left that says "Show Connection Properties".
Make a screen shot of that popup and post it here.
-
Jul 21st, 2016, 09:56 AM
#7
Thread Starter
Lively Member
Re: SQL Server 2012 connection error
Originally Posted by szlamany
If you can get into SSMS - then right-click the SERVER in the OBJECT EXPLORER and you will see a clickable link in the lower-left that says "Show Connection Properties".
Make a screen shot of that popup and post it here.
-
Jul 21st, 2016, 10:02 AM
#8
Re: SQL Server 2012 connection error
No named instance, the Connection string looks good. Are you trying to connect with VB from the same computer where the server is? or at least from where you are using SSMS?
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jul 21st, 2016, 10:09 AM
#9
Thread Starter
Lively Member
Re: SQL Server 2012 connection error
Originally Posted by kaliman79912
No named instance, the Connection string looks good. Are you trying to connect with VB from the same computer where the server is? or at least from where you are using SSMS?
Yes the same computer I am using SSMS on. Our IT department setup the server, problem is none of them have used VB.net and SQL server together so they cant really help me. As long as Visual Studio and SSMS connect they are happy. That's why I am stuck.
-
Jul 21st, 2016, 10:14 AM
#10
Re: SQL Server 2012 connection error
I suggest you do this: Add a datasource to your project and then look at the connection string it generates. It will be under Project Properties - Settings
Project - Add New Datasource
Select "Database" - "DataSet" - New connection.
From there just select your server and database. The connection string will be visible there also
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jul 21st, 2016, 10:18 AM
#11
Thread Starter
Lively Member
Re: SQL Server 2012 connection error
Originally Posted by kaliman79912
I suggest you do this: Add a datasource to your project and then look at the connection string it generates. It will be under Project Properties - Settings
Project - Add New Datasource
Select "Database" - "DataSet" - New connection.
From there just select your server and database. The connection string will be visible there also
It comes out exactly as it is in my connection code above
Data Source=Solution;Initial Catalog=DataWarehouse;Integrated Security=True
I test the connection and i get "Test connection succeeded."
-
Jul 21st, 2016, 10:19 AM
#12
Re: SQL Server 2012 connection error
OK, it's official, you have a weird situation.
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jul 21st, 2016, 10:21 AM
#13
Re: SQL Server 2012 connection error
I see you blocked out the username...
Please verify that it is DOMAIN\USERNAME
-
Jul 21st, 2016, 10:23 AM
#14
Re: SQL Server 2012 connection error
Also - try this
In a .bat file I can have this line
Code:
runas /netonly /user:domain\username "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe -S server.name.goes.here -d "
-
Jul 21st, 2016, 10:23 AM
#15
Re: SQL Server 2012 connection error
Try putting the name of the server in single quotes
Data Source='Solution'
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jul 21st, 2016, 10:24 AM
#16
Re: SQL Server 2012 connection error
Also change your connection string to have this instead of YES
Integrated Security=SSPI
-
Jul 21st, 2016, 10:25 AM
#17
Re: SQL Server 2012 connection error
Originally Posted by szlamany
I see you blocked out the username...
Please verify that it is DOMAIN\USERNAME
I think this is not relevant using windows authentication
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jul 21st, 2016, 10:27 AM
#18
Re: SQL Server 2012 connection error
Originally Posted by szlamany
Also change your connection string to have this instead of YES
Integrated Security=SSPI
This is also irrelevant regarding the exception at hand. You can omit the whole Integrated Security part of the connection string and if the server name is correct, you will get a different exception. I suggest you do not mess with other things until the resolving of the server name is fixed.
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jul 21st, 2016, 10:28 AM
#19
Re: SQL Server 2012 connection error
I had not seem the post that made the server name be the issue - where is that?
If that is the case just change it to the IP address
-
Jul 21st, 2016, 10:31 AM
#20
Re: SQL Server 2012 connection error
Originally Posted by szlamany
I had not seem the post that made the server name be the issue - where is that?
If that is the case just change it to the IP address
It is the very first one. The error message is that it cant find the server. If the Initial Catalog is wrong but the server is oK, or the integrated security is omitted or wrong you will get a "Login failed for user" error, not a "server not found".
Try using a connection string just as this:
Code:
Data Source='solution'
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jul 21st, 2016, 10:31 AM
#21
Re: SQL Server 2012 connection error
Originally Posted by kaliman79912
I think this is not relevant using windows authentication
I becomes important when "combined" with the running of SSMS through the .BAT file.
-
Jul 21st, 2016, 10:32 AM
#22
Re: SQL Server 2012 connection error
Originally Posted by kaliman79912
It is the very first one. The error message is that it cant find the server. If the Initial Catalog is wrong but the server is oK, or the integrated security is omitted or wrong you will get a "Login failed for user" error, not a "server not found".
Did we resolve the PIPES error - isn't named pipes turned off in the latest of MS SQL versions??
-
Jul 21st, 2016, 10:35 AM
#23
Re: SQL Server 2012 connection error
Still, if wrightyrx7 can connect with SSMS, and directly creating a connection with the Add Data Souce wizzard, then there is nothing wrong with the pipes. Also, this is SQL 2012
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jul 22nd, 2016, 02:49 AM
#24
Thread Starter
Lively Member
Re: SQL Server 2012 connection error
Originally Posted by szlamany
Also - try this
In a .bat file I can have this line
Code:
runas /netonly /user:domain\username "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe -S server.name.goes.here -d "
I tried the .bat file and it opened the server fine in SSMS.
Originally Posted by kaliman79912
Try putting the name of the server in single quotes
Data Source='Solution'
Same error message
Originally Posted by kaliman79912
Try using a connection string just as this:
Code:
Data Source='solution'
Same again.
Is it time for me to admit defeat?
The thing that is confusing me is that its only failing to connect when using vb.net and not when I add the server/data source to Server Explorer/SQL Server Object Explorer. And also connects fine through SSMS. I can even pull data from tables and views through to Excel and Access.
So strange...
-
Jul 22nd, 2016, 03:02 AM
#25
Thread Starter
Lively Member
Re: SQL Server 2012 connection error
Update guys....
I downloaded Visual Studio 2012 express and used the same code - Connected fine!
Im stumped!!
Guess I will just have to use this version instead of my Visual Studio Enterprise 2015
-
Jul 22nd, 2016, 03:15 AM
#26
Re: SQL Server 2012 connection error
not logical. but surely there is something wrong there.
What I was about to suggest is that you create a new project, add a dataSet, put a datagridview automatically with a table from the dataset and run it. If it does run, then change the connection string in runtime. That could give us some indication on where to go next.
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jul 22nd, 2016, 03:22 AM
#27
Thread Starter
Lively Member
Re: SQL Server 2012 connection error
Originally Posted by kaliman79912
not logical. but surely there is something wrong there.
What I was about to suggest is that you create a new project, add a dataSet, put a datagridview automatically with a table from the dataset and run it. If it does run, then change the connection string in runtime. That could give us some indication on where to go next.
Hi Kaliman,
Not sure if you seen my last update. But it is working in VS2012. So im guessing something was setup incorrect when VS2015 was installed.
So i am currently waiting for VS2015 to install then I am going to install it again.
-
Jul 22nd, 2016, 03:28 AM
#28
Re: SQL Server 2012 connection error
Yes, I did read it. But you are right. A clean install may be what you need. I use 2015 Enterprise and never seen that behavior.
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Jul 22nd, 2016, 03:32 AM
#29
Thread Starter
Lively Member
Re: SQL Server 2012 connection error
Originally Posted by kaliman79912
Yes, I did read it. But you are right. A clean install may be what you need. I use 2015 Enterprise and never seen that behavior.
Another strange thing I have just noticed.
I created 3 projects in 2015 trying to figure this out. Taking different approaches.....and as we know they didnt work because of this forum post.
When I open these 3 projects in 2012 they give me the exact same error I was getting in 2015. But when I create a new project in 2012 and use the exact same code..it works.
-
Jul 22nd, 2016, 04:34 AM
#30
Thread Starter
Lively Member
Re: SQL Server 2012 connection error
Well.....the outcome is......a reinstall worked!
It will puzzle me as to why it didn't work. But at least i can not get on with my work.
Just want to thank you all for taking the time to reply with your suggestions. Although them things wasn't the problem for me, I am sure someone else will find your suggestions useful.
Thanks again
Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|