[RESOLVED] sql connection string

Printable View

Mar 1st, 2011, 12:50 PM
GUARO

1 Attachment(s)

[RESOLVED] sql connection string

Hi gurus

I am using the following connection string to connect to a sql server on my network but having all kind of problem

here is the connection string

osqlconn.connectionstring="server=xxx.xxx.xxx.xxx,1433;" &"Database=LOS;" & "Integrated security=SSPI"

I dont know if the problem is that there is no ";" at the last parameter like the others but I get the following error that shows in the file

I hope someone can help me on this

Thanks a bunch
Mar 1st, 2011, 12:59 PM
sridharavijay

Re: sql connection string

From the error message it appears like
1) Your server is not accessible (a ping to server will help)
Or
2) If accessible, it is not configured to be not accessible from other then Local host. So it needs some configuration settings on the server so that the userid connecting to it can connect to it from remote systems.
This is true with respect to any database system as far as I know.
Hope this helps in fixing your issue

Regds
Vijay S
Mar 1st, 2011, 01:25 PM
GUARO

Re: sql connection string

S.

Thanks for jumping in on this, I think is hard all about this connection strings

1) I can ping the server fine, with no problem
2) I went and check the properties on the sqlserver, what should I be looking for in order to double check the sql server is ready to accept remote connection.

3) is this the right connection string for sql server 2000?? maybe I have the connection string all worng.

Thanks a bunch

Re: sql connection string

Try a test project with this. Fill in the "xxxxxxxx" as applicable

vb.net Code:

Imports System.Data.SqlClient
 
Public Class Form1
 
Private myConnection As SqlConnection
 
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        
        myConnection = New SqlConnection("Integrated Security=SSPI;" _
        & "Persist Security Info=False;Initial Catalog=xxxx; " _
        & "Data Source=xxxxxxxxxx")
        myConnection.Open()
    End Sub
 
End Class

Mar 1st, 2011, 02:54 PM
GUARO

Re: sql connection string

hack

do I need an & between Persist security and Initial catalog?

Thanks
Mar 1st, 2011, 02:58 PM
GaryMazzone

Re: sql connection string

NO the & is just used for string concatination here
Mar 1st, 2011, 04:14 PM
GUARO

Re: sql connection string

gary

so there should be no & within the string, is that what you are saying.

Thanks
Mar 1st, 2011, 04:16 PM
GaryMazzone

Re: sql connection string

Yes that is what I am saying.
Mar 1st, 2011, 04:31 PM
si_the_geek

Re: sql connection string

To clarify, your original code:

Code:

osqlconn.connectionstring="server=xxx.xxx.xxx.xxx,1433;" & "Database=LOS;" & "Integrated security=SSPI"

..does exactly the same as this:

Code:

osqlconn.connectionstring="server=xxx.xxx.xxx.xxx,1433;Database=LOS;Integrated security=SSPI"

...and this too:

Code:

osqlconn.connectionstring="s" & "e" & "r" & "v" & "e" & "r" & "=xxx.xxx.xxx.xxx,1433;" & "Database=LOS;" & "Integrated security=SSPI"

That is because one " ends a string, & concatenates (joins) it to something, and the next " starts another string.
Mar 1st, 2011, 05:01 PM
GUARO

Re: sql connection string

Thanks a bunch
Mar 1st, 2011, 05:17 PM
GUARO

Re: sql connection string

Gurus

Thanks for battling with me here,

I test connection and the string connection does not work

I get an error

Login Failed for user'(null)'.Reason:not associated with a trsuted Sql Server Connection".

any help ,
Thanks a bunch
Mar 2nd, 2011, 08:08 AM
GUARO

Re: sql connection string

Any other ideas gurus,
please keep battling with me on this I would really like to connect to the sql server, instead of going the access 2003 route.

Thanks again
Mar 2nd, 2011, 08:14 AM
GaryMazzone

Re: sql connection string

That mean that you are not in a domain. If that is so then you need to supply a username and password (sql log in). What version of SQL Server are you using.
Mar 2nd, 2011, 10:14 AM
GUARO

Re: sql connection string

Gary

Thanks a bunch really

1) I am using Sql 2000.

2) but I am in the domain.

This string connection gives people headaches

Thanks a bunch
Mar 2nd, 2011, 10:46 AM
Zeth643

Re: sql connection string

Quote:

Originally Posted by GUARO

Gurus

Thanks for battling with me here,

I test connection and the string connection does not work

I get an error

Login Failed for user'(null)'.Reason:not associated with a trsuted Sql Server Connection".

any help ,
Thanks a bunch

Hi.

The error means that you need to create a user in the server so that your application can use it to connect to the database and use it.

So first step is create a new user on the server, add privileges/permissions, and finally update your connection string, so it looks something similar to this :

Code:

osqlconn.connectionstring="server=xxx.xxx.xxx.xxx,1433;Database=LOS;Persist Security Info=False;User ID=MyUser;Password=MyPassword"

Simply adapt the "myuser" and "mypassword" with the ones of the created user, also, its up to you if you want to have the password on the connection string or not.
Mar 2nd, 2011, 10:54 AM
GaryMazzone

Re: sql connection string

First can you connect to the database using the Query tool with Windows auth?
Mar 2nd, 2011, 11:19 AM
GUARO

Re: sql connection string

Gurus

Thanks thanks thanks for all your help

Z.

That is what is driving me crazy, I have added in the security->loging a user to this Database "LOS"

Gary
which query tool do you recommend.

I am very new to this so I realy thanks for your patience.
Thanks a bunch gurus
Mar 2nd, 2011, 01:02 PM
GUARO

Re: sql connection string

hi gurus

I followed Gary steps based on Ze post and here is what I found

Sql server and Ws are in to different Vlan

sql Server is on 192.xxx.xxx.xxx and WS are on 10.xxx.xxx.xxx, I can ing the server in which the sql server resides but for some reason I can not connect to the sql server instance using the query tool from a WS(10), I went to one of the server(192) and use the same query tool and had no problem getting to the datbase an therefore the table.

Does this mean I am scr.... or is there a way to deal with this, I change the string connection to be use to a TPC/IP one but with no avail.

what else can I do in this case.

thanks a bunch for any help
Mar 2nd, 2011, 01:41 PM
GaryMazzone

Re: sql connection string

If the two machines are not in the same domain. Then you will need to create a SQLServer User login and give that user permissions on the database you want. Then instead of connection with Intergrated Security you supply the user name and password to use.

I can connect to differnt IP statring 10.10.xx.xxx and the server is on 15.x.x.xxx as long as my windows longin in vailid in both domains I can use Intergrated Security (as long as I am in a group) that has rights on the server any way
Mar 2nd, 2011, 02:36 PM
GUARO

Re: sql connection string

Gary

Here is the nightmare, well at least for me

ws are on the 10.xx.xx and server is on the 192, both machines are on the same domain as of now, but all traffic is allow from 10 to 192 through our Cisco vlan router, that is why 10 can see 192 but for some reason this does not work for Sql server it does not like the 10 connecting to 192,

I can ping from 10 to 192 and I can ping from 192 to 10 with no problem but again I cant see sql from 10.

what can I do
Thanks again
Mar 2nd, 2011, 02:49 PM
si_the_geek

Re: sql connection string

Have you created a login inside SQL Server, and tried using that in the connection string (as in Zeth643's example)?
Mar 2nd, 2011, 07:05 PM
GUARO

Re: sql connection string

S/ze

Yes I think I did

I went to sql server ena expanded all untill find security, then I add a user and give permission and security for the LOS database, I am using the username and password to try to connect to it, also I am the system admin in the role.

I am missing something

thanks
Mar 2nd, 2011, 09:58 PM
GUARO

Re: sql connection string

gurus

here is an update

1) I went into the server(192) and created a user within the server manage account
2) I went into security->login and add the local user to the database
3) I set the auth to sql server and windows
4) I have restart the sqlserver service everytime I make a change
5) I have used the following string connection

Code:

"osqlconn = New SqlConnection("integrated Security=SSPI;Persist Security Info=False;Initial Catalog=LOS;Data source=192.xxx.x.xx,1433")"]

also the following

Code:

"osqlconn.ConnectionString = "server=192.xxx.x.xx,1433;Database=LOS;Persist Security Info=False;User ID=susername;Password=xxxxxxx;"

and last trying to use a TCP/IP string connection

Code:

"osqlconn.ConnectionString = "data source=192.xxx.x.xx,1433;network library=dbmssocn;initial catalog=LOS;user ID=username;password=xxxxxx"

But all I get is

login faild for user "username"

shoudl I be using the full username server\username or domain\username?

maybe the problem is been in two different subnet

thanks for battling with me here ,hope you dont give up as of yet.

Thanks a bunch
Mar 3rd, 2011, 07:04 AM
si_the_geek

Re: sql connection string

The user should not have a server/domain, because it is supposed to be just an SQL Server user, not based on a Windows user.
Mar 3rd, 2011, 09:14 AM
GUARO

Re: sql connection string

gurus

Yeahhhhhhhhhhhhhhhhhh yeahhhhhhhhhhhhhh

I got in, thanks
zrid,hack,gary,zethand SI,for not giving up, thanks a bunch gurus, I realy appreciate all your help on this one and other posts.

Thanks again

yeahhhhhhhhhhhh