sqlConnection object Sql Server setup
-
Being fairly new to web development, i have an example project that runs against the adventureworks db. I have the db installed, can open it up with sql server (2005 developers edition) and can even test a connection using visualstudio server Explorer In my projects code however I get access denied error message when the asp.net app tries to access the database. I am not using IIS for this, just the webserver that runs in Vstudio IDE when developing. I suspect i don't have something setup for the database. I checked and Remote Connections are allowed Here is ex.message ex.Message "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" string The connection string looks like this strConn = get{return "Data Source=DALEHP\SQLDEVELOPER;Initial Catalog=AdventureWorks;Integrated Security=True";} The connection section looks like this: public static DataSet GetProducts(String strConn) { DataSet ds = null; SqlConnection conn = null; try { conn = ConnectionInfo.CreateConnection(strConn, true); SqlCommand cmd = new SqlCommand("SELECT * FROM PRODUCTION.PRODUCT", conn); SqlDataAdapter sqlDA = new SqlDataAdapter(cmd); ds = new DataSet("Products_Set"); sqlDA.Fill(ds); } catch(SqlException ex) { System.Diagnostics.Debug.WriteLine(ex.Message); throw ex; } finally { ConnectionInfo.CloseConnection(conn); } return ds; }
-
Being fairly new to web development, i have an example project that runs against the adventureworks db. I have the db installed, can open it up with sql server (2005 developers edition) and can even test a connection using visualstudio server Explorer In my projects code however I get access denied error message when the asp.net app tries to access the database. I am not using IIS for this, just the webserver that runs in Vstudio IDE when developing. I suspect i don't have something setup for the database. I checked and Remote Connections are allowed Here is ex.message ex.Message "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" string The connection string looks like this strConn = get{return "Data Source=DALEHP\SQLDEVELOPER;Initial Catalog=AdventureWorks;Integrated Security=True";} The connection section looks like this: public static DataSet GetProducts(String strConn) { DataSet ds = null; SqlConnection conn = null; try { conn = ConnectionInfo.CreateConnection(strConn, true); SqlCommand cmd = new SqlCommand("SELECT * FROM PRODUCTION.PRODUCT", conn); SqlDataAdapter sqlDA = new SqlDataAdapter(cmd); ds = new DataSet("Products_Set"); sqlDA.Fill(ds); } catch(SqlException ex) { System.Diagnostics.Debug.WriteLine(ex.Message); throw ex; } finally { ConnectionInfo.CloseConnection(conn); } return ds; }
There are two ways of doing integrated sql security. If you want to set permissions for each user (which I don't recommend) make sure you go into IIS and uncheck the allow anonymous access box for your web application. You will also need in your web.config. What I do in my applications is create one windows account that has access to the database. Use a system account so that no one can use that account to log on to the machine. Then add the following your web.config Then make sure the yourDomain\yourSQLAccountUserName account has appropriate rights in the database. Then if you want to do auditing you can always pass the userName of your current user in a sql parameter and update the table. Also put your conn string in the web.config and use system.configuration.configurationmanager to get it. Another thing is that it is best to use using blocks. Then you don't have to call close or dispose methods. For example public DataTable GetDataTable(string ProcName, SqlParameter[] parameters) { DataTable dt = new DataTable(); using (SqlCommand cmd = new SqlCommand(ProcName)) { cmd.CommandType = CommandType.StoredProcedure; using (SqlConnection conn = new SqlConnection(connString)) { cmd.Connection = conn; foreach (SqlParameter prm in parameters) { cmd.Parameters.Add(prm); } SqlDataAdapter da = new SqlDataAdapter(cmd); cmd.Connection.Open(); da.Fill(dt); cmd.Parameters.Clear(); } } return dt; } I didn't get any requirements for the signature