0

Update: This is fixed thanks to some great help here. If you're having similar issues do the following:

  • Enable ALL exceptions before running
  • Figure out the exact error you're getting when trying to connect
  • Make sure that all users on SQL side have a user assigned to a login for that specific database

I've got a very nice program I've made that I'm ready to start pushing out for testing. The problem is that when I coded all of this I simply used the Integrated Security which uses my windows login to connect to the SQL Server. That worked fine as I'm a sysadmin on that whole server, including the database in question.

Now I'm ready to push this out to other users and I need to change the code to either use my login for all users (hard coded to the connection string) or use a new login specifically for that database.

I've used the following connection string and it won't work! I've also added the server to the data connections section of the server explorer but I guess I don't know how to tell the program to use that connection in my code.

I've obfuscated the password in the code below but it's written out in my code. No errors or anything when running - it just doesn't connect or pull data.

Thoughts? Thanks!

Dim strSQLConn As String = ("Server=Hertz1455;Database=AbsenceApplication;User ID=hac0421;Password=********")

Update - hertz1455 is the server name, there isn't a port that I need to use. Below is the rest of the code for when the program starts. I've also commented on some answers with the error I'm getting.

Dim strSQLConn As String = "Fleet_PTO.My.MySettings.AbsenceApplicationConnectionString"
Dim strQuery As String
Dim sqlSQLCon As SqlConnection
sqlSQLCon = New SqlConnection(strSQLConn)
    Dim cmd As SqlCommand = New SqlCommand("SELECT person FROM tblSupervisor", sqlSQLCon)
    sqlSQLCon.Open()
    Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
    Dim myDataTable As DataTable = New DataTable
    myDA.Fill(myDataTable)
    sqlSQLCon.Close()

When I change the strSqlConn to the string below, everything works just fine.

Dim strSQLConn As String = ("Data Source=Hertz1455;Initial Catalog=AbsenceApplication;Integrated Security=True")
Improve this question
3
  • 1
    Can you show an example of the code you're using to try establishing and using a connection based on this string? I find it hard to believe that no errors occur. If your connection string is invalid for whatever reason, then SqlConnection::Open() should throw. It doesn't just silently decide not to connect. msdn.microsoft.com/en-us/library/… Commented Mar 6, 2018 at 17:35
  • 2
    Is Sql Server setup to allow mixed authentication? Commented Mar 6, 2018 at 17:36
  • Joel - that's a good question. I'll check. EDIT: Yes, mixed mode is turned on for that server. I was able to use the username and password I want and log into the server and query via SSMS Commented Mar 6, 2018 at 19:16

2 Answers 2

Reset to default
1

We can only guess since the question is a bit unclear without any exceptions or warning messages.

Try this:

  1. Create a new text file anywhere. (say Desktop)
  2. Rename the extension to udl and open the file.
  3. You should be seeing this window.
  4. Test your connection, see if you can connect.

Lat94 might be correct with the Server=Hertz:1455 part although it's not the default MSSQL port.

Also check your server's firewall, it might be blocking the connection request. You might be getting an exception (like connection forcefully rejected) but it might not be popping up. Check exception settings, enable everything since you find a relative clue.

Note: Dear future viewer, please follow the comments.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I'm sure an exception or error is coming up but I guess I'm nut sure where to find it. I do show this on the debug output - Exception thrown: 'System.ArgumentException' in System.Data.dll
@cardinalsfan You are almost there, that's probably your problem. Can you please CTRL + SHIFT + E, check all the exceptions, and try debugging again?
Ok - now I'm getting somewhere. I enabled all exceptions and now I'm getting - 'Login failed for user 'hac0421'.' New Exception as well - System.Data.SqlClient.SqlException:
@cardinalsfan Two possibilities: Either the password in your conn string is not correct, or your user is not allowed to connect to SQL: itproguru.com/wp-content/uploads/2014/09/image15.png
If you are not absolutely sure that both of the above are not the case, please go back to the udl method and test your credentials.
|
1

Perhaps you should add the driver you're using in order to get it done, like:

Dim strSQLConn As String = ("Server=SQLOLEDB;Server=Hertz1455;Database=AbsenceApplication;User ID=hac0421;Password=********")

Is "1455 at Server=Hertz1455 the port number? Shouldn't it be Server=Hertz:1455 in that case?

Check this link for more info. It is in C#, but it will not be a problem.

Improve this answer

2 Comments

It would be Hertz,1455 not Hertz:1455 if it were the port number
hertz1455 is the name of the server

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.