Hello,
I need help to connect to my local sql express db in visual studio 2003 in vb.
It currently works with the production db information and I need to test a few things, So i'd like to point it to the local db.
It works this way:
Const CONN_STRING = "server=johndoe;user id=john;password=doe;database=johnd"
Dim conn As SqlConnection = New SqlConnection(Me.CONN_STRING)
conn.Open()
I want to point it to my local db and it doesn't connect.
Dim connectionString As String = "Data Source={myusername\SQLEXPRESS};Initial Catalog=johnd;Trusted_Connection=yes"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Try
conn.Open()
Catch ex As Exception
Response.Write(ex.ToString())
End Try
and even that doesn't work.
When i go to my sql server 2005, it says
MYUSERNAME\SQLEXPRESS (SQL SERVER 9.0.3042 - theworknetwork\myusername
what am i doing wrong ?
Thanks.
You can try two things:
1) If you are an administrator on your local computer, then make sure that your user ID is in the Administrators group and then make that the Administrators group has access to the SQLEXPRESS instance. Change the user ID used by the SQL Server Management Tool with your local machine login, not your network login. If this doesn't work, replace it with the local machine Administrator account.
2) Instead of using Windows authorizations, use SQL Server authorizations. Create a new user in SQL Server specifically for your database. Make this user the dbo. Then use this user name with password in your ConnectionString.
At times, I have issues with Windows users because the passwords are different across the various computers. This has not been the case with SQL Server credentials, which I usually prefer.
Also, try using Integrated_Security in place of Trusted_Connection in the ConnectionString.
|||
Heres what I've tried. I went to that db (johnd), went to the security>users> and there is already a user called dbo
I tried doing Integrated_Security like you said and i got an error saying that is an invalid key. So here is what I have and it still doesn't work.
"Data Source={myusername\SQLEXPRESS};Initial Catalog=intranet;Trusted_Connection=yes;user id=dbo; password=dbo"
Any ideas?
|||
Can you please try with the following connection string if you are using NT Authentication:
Data Source={localmachine\SQLEXPRESS};Initial Catalog=intranet;Trusted_Connection=yes;
If you are using SQL Server User please try with the following connection string:
Data Source={localmachine\SQLEXPRESS};Initial Catalog=intranet;user id=<local sql server user>; password=<password>
Please make sure that you are changing the bold string with appropriate values.
Following URL provides you the steps to create the user:
http://www.microsoft.com/technet/prodtechnol/sql/2005/mgsqlexpwssmse.mspx
|||
I am in microsoft sql server managemen studio Object explorer (on left)
The very first item shows SDAVID\SQLEXPRESS
underneath that i have my database intranet
I then went to security > users [right click] > New User
Under Database User - New,
User Name: SDAVID
Login name
a new window came for Select Login and i found SDAVID [hit okay.]
* so now it shows User name: SDAVID
and login name shows SDAVID
under schemas owned by this user, I choose db_owner and db_securityadmin
under database membership i choose db_owner and db_securityadmin
Now under the code, here is what i have...
Const dbstring = "Data Source={SDAVID\SQLEXPRESS};Initial Catalog=intranet;user id=<sdavid>; password=<sdavid>"
Public Function testConnection()
Dim connectionString As String = "Data Source={SDAVID\SQLEXPRESS};Initial Catalog=intranet;user id=<sdavid>; password=<sdavid>"
Dim conn As SqlConnection = New SqlConnection(ConnectionString)
Dim cmd As SqlCommand = New SqlCommand("selec * from tblUsers", conn)
cmd.CommandType = CommandType.Text
Try
conn.Open()
Catch ex As Exception
Response.Write(ex.ToString())
Finally
conn.Close()
End Try
this generates the following error:
sqlException: Sql server does not exists or access denied.
thanks.
|||
Can you please use the following modified connection string:
Const dbstring = "Data Source=SDAVID\SQLEXPRESS;Initial Catalog=intranet;user id=sdavid; password=sdavid"
|||
Thank you. that did it! ..
Data Source=SDAVID\SQLEXPRESS;Initial Catalog=intranet;Trusted_Connection=yes;
|||
Hey Sri,
I tried it your way with specifying username and password and that did not work. Do you know why this could be ?
in Micrososft Sql Server Mangement Studio, i went to the db, Security > USers and created a user called sdavid with sdavid. I gave the user db_datareader and db_owner an under database roles membership gave it db_owner... i'd like to get the app working sql sever authhentication.
thanks.
