SQL Server 2005 express connection error

[kadilacgh]

Hi

am working on a project and am using sql server 2005 express as my database server, the problem is i can;t connect to the sql server from any machine on my local network (windows).

Below is my connection string

con.ConnectionString = "Data Source=webserver1\SQLEXPRESS;AttachDbFilename='C:\POSM_DB.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True;"

It works all right on the development machine but not on network, i think it has something to do with the network.

Any suggestions ?

[sknake]

What connection error are you receiving?

[kadilacgh]

Thanks for the reply

srry for includeing the error msg in the post
The connection error is something like this :

"login failed for user webserver1/guest"

the name of the sql server pc is webserver1.

[sknake]

Are you trying to connect from a web application? You need to specify ";Integrated Security=False;User=username;Password=Password;" in your connection string if you're not using windows authentication.

[kadilacgh]

yep, the project is not a web application its a desktop app and am using vb.net 2005 professional.

The authentication method is windows, i thought this code snippet specify that "Integrated Security= True"

By the way which one would u prefer , windows auth or sql server auth ?

Thanks

[Geekitygeek]

I had a similar issue when i started using SQL Server...took a while to figure out but in my case it was a combination of two things:

1) SQL Server's settings default to not allowing remote connections. Instructions for turning this on can be found here: http://support.microsoft.com/kb/914277

2) whether you choose to go with windows authentication or sql login, you need to make sure that you have granted access permissions in SQL Server.

Cant recall the specifics of permissions in 2005, i use 2008 now. I can dig up some instructions if you need them...try 1) first :)

[sknake]

Here are some functions to create connection strings with and without SQL Authentication. I'm sure you can convert this to VB easily ;)

public static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }
    /* -------------------------------------------------------------------- */
    public static string BuildSqlNativeConnStr(string server, string database, string username, string password)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=False;User Id={2};Password={3};", 
        server, 
        database,
        username,
        password);
    }

[kadilacgh]

Thanks guy's

I would try ur suggestion

But Ryshan , can u show me hw to grant access permissions in sql server, do i have to use a special software for that, like sql manager or something....?

[TomW]

public static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }
    /* -------------------------------------------------------------------- */
    public static string BuildSqlNativeConnStr(string server, string database, string username, string password)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=False;User Id={2};Password={3};", 
        server, 
        database,
        username,
        password);
    }

No need to build your own functions, there's an sqlConnectionStringBuilder method built in that does this for you.

Dim conBldr As New SqlClient.SqlConnectionStringBuilder

        With conBldr
            .DataSource = "ServerName"
            .InitialCatalog = "DataBaseName"
            .IntegratedSecurity = True
        End With

        MessageBox.Show(conBldr.ToString)

[kadilacgh]

wow, thats great, like me try that , tx sknake

[sknake]

Did the solution work for you? If it did then please mark this thread as solved if your question was answered, and if it didn't then we look forward to hearing from you. Good luck!

[kadilacgh]

Tx sknake,

hhhhhhm Infact am still struggling with it, i v been able to get a connection to the sql server but another error is being displayed, the error says:

"user does not have permission to perform this action"

U see i was login in as guest , so i had to grant administrator privlages to the guest acct on the sql server.

i think it has something to do with permissions, so am trying out something with sql server management express edition. Hope it would work for me,

By the way does any one knw anything about granting permissions using sql server management express edition

[saradha]

hi,
did u try logging in to the sql server from some other machine on the local network using sql server 2005 installed in that local machine.
If u get an error something similar to "........Remote connections disabled to the Data base you are connecting .........." then u can solve the problem by creating a new log in to the Data base and with "enable Remote log in" checked . I also had similar problems and above suggestion solved my problem.The new log in i created had sql server authentication. This i did with sql server management studio express

Any doubts feel free to ask :)

[kadilacgh]

Tx guys, i v been able to break the jinx

I change the connection mode to sql server auth, created a new login (UN and PW) for the database

Then i grant the new login acct full server permission and voila, it worked like magic

This is the connection string i used

Datasource = webserver1/SQLEXPRESS,  Initial Catalog=POSM_DB; Integrated Security=SSPI; User Id=myUsername;Password=myPassword

Thank you very much guys for ur help and time, really appericate it

[saradha]

Tx guys, i v been able to break the jinx

I change the connection mode to sql server auth, created a new login (UN and PW) for the database

Then i grant the new login acct full server permission and voila, it worked like magic

This is the connection string i used

Datasource = webserver1/SQLEXPRESS,  Initial Catalog=POSM_DB; Integrated Security=SSPI; User Id=myUsername;Password=myPassword

Thank you very much guys for ur help and time, really appericate it

thats good .. hope u marked ma reply as correct aanswer ..

[parthibanrock]

Did the solution work for you? If it did then please mark this thread as solved if your question was answered, and if it didn't then we look forward to hearing from you. Good luck!

it cannot be work the connection string error in the datasoruce.

the server name is different my system to another system.

datasource=compaq/sql express

the compaq is differ from other system

eg

datasource=B4546FGHU46G/sql express

how to solve it

[sknake]

Stop burning up the forums with the same question. Start a new thread and wait for an answer. You have asked the same question ~8 times in the past 30 minutes.