I would think it would make more sense to only give access to the application to access the sql database and remove all other access. So if you application is using a sqluser name and password for its connection. Only give that user access.
If that is not an option. Then another possibility is to make sure all of your database access for you application happens through stored procedures. In which case the user would not need select access to any of the tables only execute rights on the stored procedures.
Thank you Ben,
it's good idea and if I distribute database file than the stored procedures are enclosed in database files and with rights too?
I read about encrypting, but it is only for column not all database.
When you distribute the database you can set up the user rights to the tables. Unfortunately, if they are the ones that installed sql server then they would have an SA account which would allow them to change their access and read their tables.
I think if you can not control your users access to your database, you will have trouble short of encrypting each column which would affect performace.
I have seen other programs like Microsoft great plains, where all of there access is through stored procedures which are encrypted. Then all the tables have names that are like IV10001. That way it is not easy for users to understand what the table is. Still if your users can or will have SA access to your database they will be able to decrypt the storedprocedures.
Hi guys, I've got this problem, hope someone can help me out.
I've written and application and it works fine on my computer and i can connect to SQL through a Text file
connection string in the Text box : Data Source= NameOfMyDataSource;Initial Catalog= DataBaseName ;Integrated Security=True
As I said, it works fine on my computer but NOT on other computers !!!
I installed my application on another computer and also installed SQL server, then attached my database and it works fine, but when i run my progran and enter ID and Pass get this error !
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
By default, SQL Server blocks remote access. To get round this, log into SQL Server Configuration Manager and make sure that you have enabled Named Pipes. Then, go into the Surface Area Configuration Tool and make sure it's set for Local and/or remote connections for Named Pipes. Now, restart the SQL Server instance.
Deja View - the feeling that you've seen this post before.
Hey everyone! I'm a little stumped on a problem with some SQL code that I'm writing, so I was hoping someone might be able to offer some advice.
I have a varchar column on a table in which I record the id_keys of another table. Sometimes multiple id_keys are entered into the column, which we format as: 1; 2; 3, with each id_key seperated from the previous one by a semicolon with a space (; ). What I'm trying to do is to write an update statement that will pull the numeric values of the id_keys out of the varchar string, and then match them up to the id_keys of the first table. This is the basic statement I've got so far:
SET column1 = a.column1,
column2 = a.column2
FROM TABLE2 a
WHERE TABLE1.id_key = (
Column3 is the column that would have the varchar string containing the id_keys of TABLE1. Obviously, simply doing a SELECT statement on column3 won't be sufficient, but I'm not sure how to write my SQL code to give me the data I need. I suspect that I'll also have to convert the data back to numeric from varchar, but I haven't got that far yet.
Well, the tables that I'm trying to do this on are part of a commericial sales database, not one that I've developed myself. On the table that I'm saving the id_keys in, a row is only inserted when a customer makes a purchase (the id's represent vouchers that can be redeemed as part of the purchase). As a result, a customer might redeem multiple vouchers as part of a single sale. I would use more columns, but the table only allows me to use up to 10 custom defined columns, all of which I have data feeding into.
As far as appending the same type of string in another table, I'm not quite sure I follow. Could you elaborate? (I don't actually have any training with sql, so I'm figuring this out as I go, with a little aid from Microsoft's "Querying Microsoft SQL Server 2000" book.) Thanks!
SELECT * FROM Task_Questionnaire WHERE (DAG_Flag='D') AND (TIR Like 'T***-***') ORDER BY RecNumber;
This works in access, however, once on my dot net page, and I call it, it doesnt. In all actually I am just trying to get were TIR = NULL, I've tried Is Not Null, and Not Null, and that doesnt seem to function either!