Topic: Problem connecting to sql database from C#

[lanks]

The code works from my own computer, in VS2008 and deployed. When i deploy it to other computers it returns an error.
The other computers are installed with .net framework 3.5 and sqlserver 2005. sqlbrowser is running and the user can access the server (named SERVER01)

I am given the following error;
System.Data.SqlClient.SqlException: Cannot open database "MyDatabase" requested by the login. The login failed. Login failed for user 'Comp1/SteveM'

Below is a snippet of my code

Code: [Select]

        SqlConnection Connection = new SqlConnection(@"Data Source=SERVER01\;Initial Catalog=Database;Integrated Security=True;");
        ArrayList Username = new ArrayList();
        ArrayList Password= new ArrayList();
            try
            {
                Connection.Open(); //Trigger Open Event
                SqlCommand Cmd = Connection.CreateCommand(); 
                Cmd.CommandType = CommandType.Text;
                Cmd.CommandText = "SELECT Username, Password FROM Users";
                using (SqlDataReader reader = Cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Username.Add(reader["Username"]).ToString());
                        Password.Add(reader["Password"]).ToString());
                    }
                    reader.Close();
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                Connection.Close();
            }

Thank you in advance.

[Xander]

To me that error sounds like a permission request.  If the database you are attempting to access needs authorization, you need to specify it.

[lanks]

To me that error sounds like a permission request.  If the database you are attempting to access needs authorization, you need to specify it.

Would that mean that no one could get into it, but my computer can but others cant, they all have the same updates.

[Glenn R]

Definately sounds like a permissions issue as previously stated.

[Tuoni]

Sounds like it could be due to Windows Authorisation, rather than SQL Authorisation on the database (It attempts to login using the user's details, rather than allowing you to provide details in the connection string).  Are you logging in as a different user on all the other computers?

[lanks]

Sounds like it could be due to Windows Authorisation, rather than SQL Authorisation on the database (It attempts to login using the user's details, rather than allowing you to provide details in the connection string).  Are you logging in as a different user on all the other computers?

Yes, different users for each computer. 3 computers can access it but all the others can't.

If this is true how do i change it so it takes my connection string over the users login details?

[Tuoni]

Sounds like it could be due to Windows Authorisation, rather than SQL Authorisation on the database (It attempts to login using the user's details, rather than allowing you to provide details in the connection string).  Are you logging in as a different user on all the other computers?

Yes, different users for each computer. 3 computers can access it but all the others can't.

If this is true how do i change it so it takes my connection string over the users login details?

Go to your SQL Server Managment Studio, right click on your server and click Properties.

Go to Security and select "SQL Server and Windows Authentication Mode".  Once you have set that, I suggest you create a new user for your database (one which is not a "real" login, the connection string is in plain text) then add a "user=" and "pass=" field to your connection string.

[lanks]

Sounds like it could be due to Windows Authorisation, rather than SQL Authorisation on the database (It attempts to login using the user's details, rather than allowing you to provide details in the connection string).  Are you logging in as a different user on all the other computers?

Yes, different users for each computer. 3 computers can access it but all the others can't.

If this is true how do i change it so it takes my connection string over the users login details?

Go to your SQL Server Managment Studio, right click on your server and click Properties.

Go to Security and select "SQL Server and Windows Authentication Mode".  Once you have set that, I suggest you create a new user for your database (one which is not a "real" login, the connection string is in plain text) then add a "user=" and "pass=" field to your connection string.

This worked, that you.

It still makes no sense as to why it worked on 3 computers without a username and password but the rest didn't

[visualassembly]

It still makes no sense as to why it worked on 3 computers without a username and password but the rest didn't

Windows authorization is the default security mode in SQL Server, so the 3 computers that it worked on, the user probably had elevated privileges (or the group or domain that the user is a part of is trusted).  In this mode, SQL Server will not check for a username/password, because it's relying on Windows to tell it who is OK and who is not. 

In mixed mode (which you have it set to now), the users that are trusted still do not require username and password, but all other users do.