Printable View
Hey,
I need some good advice please. I have finished building my Windows Application and it has a login interface for users. But I have no security as yet for the SQL SERVER database.
What is the best secure way I can have for the DB? The database will be on an intranet.
thanks
With it's default installation outside users cannot connect to the "service" that exposes the database to the network.
If the database itself is on a real "server" that is running a "server O/S" then the physical database is secure.
Then it's a matter of how you "open" access to your users.
Two questions:
1) Is this a "true" domain - in which ACTIVE DIRECTORY is running on a DOMAIN controller?
2) Are you using SQL authentication or WINDOWS authentication on the SQL server?
Please answer these two questions and give a bit more info on what you do with the "login credentials" that the user gives on your login interface.
Well at the moment I have everything on my computer but eventually it has to go on the intranet server. For my dissertation I have to discuss how I shall be securing the database.
About the server it runs on a Windows Server 2003 and that is all that I know. So I argue with this finding the best security I can have.
The SQL SERVER database has a windows authentication plus a login interface which has the attached procedure.
I am attaching how I get the login credentials in a .txt.
Accidentally I have done something to the code and it is not getting me to the next Form for no type of user. Cant figure out what I have done. Can you spot why please ?
That TEXT FILE doesn't open for me - what is it supposed to be?
Try again please, Ive replaced it
Is Administration_Menu a menu item or a form? And have you actually stepped through your code to see where execution takes you?
Ok - from what I can see your ask for login id and password - basically that gets passed to a stored procedure.
All this is done after connection - so this is not for security purposes.
This id/pw is for application rights - I am assuming that based on the id/pw entered certain rights are granted to the user.
I see you are saving the user id - how are you using it later on in the code?
But back to your original question - database security.
Please show your connection string. It would be a waste of my time discussing best practice without seeing how your connection string is setup.
here is my connection string.
Code:Public myConnstring As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\XXXX\Microsoft SQL Server\MSSQL.1\MSSQL\Data\BooksControl.mdf;Integrated Security=True;User Instance=True"
As yet it is on my local PC, however I dont know how to improve on that with roles and permissions as this is my first attempt.I am assuming I need to give roles and permissions on the table, but have no idea how to do that and then connect these roles to my application
I appreciate it v much if you can give me some advice about how to deal with this security issue and an example of how to do it.
thanks a lot. much appreciated
Hi
The login error is solved.
What I require is now some advice on the security issue mentioned above.
thanks
When your database becomes "server based" that connection string will not be appropriate.
First the ATTACHDBFILENAME means that the service on your local machine doesn't attach the DB until you open it. SQL DB's on true servers are "fully attached" at all times. You can't you ATTACHDBFILENAME on a true server.
And of course you don't refer to them by the "filename" - instead you refer to them by the DB name (in the connection string).
Second the user instance doesn't work on a true server. That tells a local sql service that it should "start and become part of just that single user". That means that an instance of SQLSERVER.EXE starts running just for this application. Rather single-user like.
With all that said - you should probably ATTACH the DB fully even on your local machine right now. And change the CONNECTION STRING to get rid of the ATTACHDBFILENAME and the USER INSTANCE parts now. They don't fit with an app that will potentially run on a true server.
Once that is done it's easier for us to discuss future security - since the model you have on your development machine more properly matches the model you will have in production.
btw - the INTEGRATED SECURITY - that stays. That means you are using WINDOWS AUTHENTICATION - which is a good thing.
thanks Syzlamany,
I had assumed I would be replacing it. As this is my first attempt of a DB on a server can you give me a good link on how to do that please?
P.S. Here is my attempt:
Public myConnstring As String = Driver={SQL Native Client};Server=MyComputerName;Database=BooksControl;Integrated Security=True;
1. What goes into SQL Native Client cos the intellisense say : Expression expected
2. Do I do "MycomputerName" or MyComputerName
When I copy it on a CD does it automatically attach to the application.EXE or would that need to change then?
If you are using SQLConnection object than you dont need to specify Driver details.Quote:
Originally Posted by angelica
If computer name is Hardcoded in your code,that YES you need to change it in your code,it would be better to use Config file to store this type of information.Quote:
Originally Posted by angelica
Do you have Management Studio Express?
The data source - for the purposes of this experiment - stays the same. You are still opening it on your local machine.
Just get rid of the two items I mentioned.
If you need to see connection string examples go to www.connectionstrings.com
Can someone pls tell me if this's on the right track. I have an intellisense error saying : identifier expected.
Code:Imports System.Configuration
Imports System.IO.StringWriter
Module Module1
Public connstr As String = ConfigurationManager.ConnectionStrings["MyDbConn1"].ToString()
Public MyConn As New SqlConnection(ConnStr)
This is what I have in the app config
Code:<connectionStrings>
<add name="MyDbConn1"
connectionString="Server=Angelica;Database=BooksControl;Trusted_Connection=Yes;"/>
</connectionStrings>
We have strings like this in our web.config (asp.net app)
Is your machine name ANGELICA? That is a SQLEXPRESS instance - right?Code:<connectionStrings>
<!--<add name="ConnectionString" connectionString="Server=FPS-LAP-SZ\SQLExpress; Initial Catalog=Stufiles; Integrated Security=SSPI"/>-->
<add name="ConnectionString" connectionString="Server=FPSSQL05; Initial Catalog=Stufiles; Integrated Security=SSPI"/>
<!--add name="ConnectionString" connectionString="Server=director\sqlexpress; Initial Catalog=Stufiles; Integrated Security=SSPI"/>-->
</connectionStrings>
Try making yours be
That trailing ; (semi-colon) could be your whole problemCode:connectionString="Server=Angelica\SQLExpress; Initial Catalog=BooksControl; Integrated Security=SSPI"
Hi Slz,
Im still with the same error. I dont know which ; you are referring to.
My error is in the VB at the moment , in the red indicated cos I havent tried the connection yet.
Public connstr As String = ConfigurationManager.ConnectionStrings["MyDbConn1"].ToString()
any idea?
In my aspx.vb code we do this
Code:dcn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("connectionString").ToString
Now I get no errors but I cannot connect. Do I need to do anything else?
I get this message: Cannot open database "BooksControl"
When I tried to add computerName\user to the string in the app config I got a different error which is jpg 2
how is this done properly pls? What am I missing?
I asked a few posts back if you had SQL SERVER MANAGEMENT STUDIO - EXPRESS.
Do you?
You need to permanently attach the database - have you done that?
It's done with SSMS Express.
Yes I have it. The database has been connected through the SQL Server Management all the time. Now I made a new connection which still shows up to be as below . Even in the Server Explorer, on the application side it shows the same i.e. C:\ .....
Do I need to change some setting there pls. Can you give me instructions if so?
C:\XXXXXXXX\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\BOOKSCONTROL.MDF
Let's do everything from SSMS...
You see your server in the object explorer pane on the left - open the DATABASE's branch.
Right-click your database - BOOKSCONTROL - select PROPERTIES.
Select the FILES page - you will see a logical name for the DATA and a logical name for the LOG - scroll to the right.
What is the PATH for both of these logical files?
Is it the
C:\XXXX\Microsoft SQL Server\MSSQL.1\MSSQL\Data\BooksControl.mdf
Yes, that's it
Ok - so we just verified two really important points.
Your database name in SSMS is BooksControl - right?
and we verified that the BooksControl database is located at the same file path you have been using all along.
Ok - let's talk about the first error you posted.
The LOGIN FAILED error...
Go back into SSMS - right-click the SERVER at the top of the OBJECT EXPLORER - select PROPERTIES. Go to the SECURITY page - what is checked off for SERVER AUTHENTICATION?
And also - at the bottom left of that page is VIEW CONNECTION PROPERTIES - click that.
What is the AUTHENTICATION METHOD?
What is the USERNAME?
What is the COMPUTER NAME?
Username : ANGELICA\user ---ANGELICA is CAPS
Authentication: Windows
Computername : ANGELICA
Ok - back into SSMS - open a query window.
Change the DATABASE dropdown to your DATABASE.
Execute a simple query - does will it work?
Yep queries work.
And also from my VB I can access the DB BUT with the C:\...... connection showing
not sure what could be wrong (szlamany's post #14 should have worked),just try passing User Autheintication to check if you are able to connect to SQL server you not
try with this
Server=ANGELICA\SQLExpress;Database=BooksControl;User ID=ANGELICA\user;Password=your machine pwd;Trusted_Connection=True;
But how do I do that? Sorry if it sounds silly, but dont quite know
Code:<connectionStrings>
<add name="ConnectionString" connectionString="Server=ANGELICA\SQLExpress;Database=BooksControl;User ID=ANGELICA\user;Password=your machine pwd;Trusted_Connection=True;
"/>
</connectionStrings>
Rit,
same error as connectionerror2.jpg
You cannot put a un/pw in a connection string and use trusted/SSPI connections (WINDOWS AUTHENTICATION) - that does not work.
Ang - you posted two JPG error messages - I wish I knew which one was occurring when...
Please post the CONNECTION STRING we are using that's getting errors - and let's stick to that one while we figure this out.
At any rate - the second JPG talks about surface area config problems.
To diagnose that go into START/PROGRAMS/MS SQL/CONFIG TOOLS/SQL SERVER SURFACE AREA CONFIG
Surface Area for Configuraton...
Click on REMOTE CONNECTIONS - Local and Remote connections should be checked off - is it?
Let me check this out pls first cos I had accidentally deleted the connection in the App Config. Are these OK?
App Config
Code:<connectionStrings>
<add name="MyDbConn1"
connectionString="Server=Angelica;Database=BooksControl;Trusted_Connection=Yes;"/>
</connectionStrings>
VB Code:
Code:Public connstr As String = System.Configuration.ConfigurationManager.ConnectionStrings("MyDbConn1").ToString()
Public MyConn As New SqlConnection(connstr)
is it possible that I have a password in the DB which I dont know, maybe when installing the SSMS. Just in case can I check this out? and how?
Angelica is not the name of the server
It's Angelica\SQLExpress.
Don't you see this when you connect with SSMS? It's the first thing that it asks you - right?
Fix the connection string. You are specify the wrong sql server name - which is exactly what the error message is telling you.
Gosh ,
it seems hectic to get this connection cos now I get the same as the first error which is :
P.S. Do I need to delete the connection from the VB, does it make the connection at runtime?
Have deleted it and still get the error below.
It's not hectic - you are all over the place...
You are making progress - then getting errors - then backing up over your progress and going a different direction!
You are now connecting to the service - and instead are getting an authentication error.
Why are your "whiting out" the domain in that image posted? Is it not the same ANGELICA\USER that was working for SSMS back in post #24?
Or are you using a DOMAIN and not telling use that the domain name is not ANGELICA? I don't need to know the name - if you need to protect it - but please we are trying to work through this one simple step at a time...Quote:
Originally Posted by angelica
If the error message said domain XXXXXXX\user then you need to add that in SSMS under security - which was the whole point of this thread - how to add proper security to the database...
But I'm making assumptions - please give us more information.
Ok so you say I need to add XXXXXX\user in the SSMS security. I had a look and I get this: jpgQuote:
If the error message said domain XXXXXXX\user then you need to add that in SSMS under security - which was the whole point of this thread - how to add proper security to the database...
So how do I go from there pls?
First - from that image I can see that the DATABASE is not attached properly. You are supposed to right-click the DATABASE branch and SELECT ATTACH. I've never seen a DB attached with a full file path right in the OBJECT EXPLORER.
I think you will want to fix this first. Doing the next step without dealing with this problem will cause us to get no where fast...
Second - the security - right-click on the xxxxxxx\USER item under the security branch.
You need to work up the properties of this user - making sure they have access to the DB - all this is available under the properties.
[edit] Under the GENERAL PAGE you can select a DEFAULT DATABASE - make it be BooksControl. Don't do this - it will create a major problem for you if you don't have the BOOKSCONTROL DB attached properly - DO NOT SET THE DEFAULT DATABASE [/edit]
Then under the USER MAPPING page you check off the DB the user is mapped to.
In addition to what's being setup at the DB server, pinging helps eliminate/trace client or network related issues. We do a tnsping on our Oracle DB listener... I don't know what the comparable utility is for an SQL Server.
Hi Szlamany,
1. Cant see the User Mapping Page!.
2. I have also changed the authentication to a password/ID in the connection and set up a user where the 3 categories will connect with. Do you think this is a good idea, since I have to expoprt the DB and the compiled application on a CD.
Leinad,:
3.what is this for? Sorry simple terminology pls its the first time my using a SQL SERVER.!Quote:
pinging helps eliminate/trace client or network related issues. We do a tnsping on our Oracle DB listener
I removed the domain\ name in my post - I'm guessing you removed the image for that reason...
When I go to properties for a user under the security branch I get 5 Login Property pages - General, Server Roles, User Mapping, Securables and Status. Maybe it's SSMS Express that's making this different for you. But you should still be able to find a page where you can map the login to a database.
Did you try to re-attach the db differently yet?
I dont have User Mappings. Guess as you said its not included in the Express Edition.
About my attaching the DB I had done it through the normal way, DB > Attach normally and it shows the path to the Db. Then a friend suggested that I type in the name of the DB instead of the path and it worked. Then I got blocked as you said with the Default, but managed to fix that somehow. This was before I got your post and then I confirm it true.
What's the user mapping for? And what if I dont set it off?
The user mapping is just a way to associate a username with a DB - which seems to be your issue.
I've got zero experience with EXPRESS - I've got SQL 2005 EXPRESS installed on some machines I've got but not the SSMS EXPRESS tool...
At any rate - now that the DB is attached (and we are going to leave it attached like this!) can you open it in the object explorer and see a branch for SECURITY - and then USERS?
Under that USERS branch you can right-click and add a user. There should be two spots - username and login name - the login name has a button with "..." on it - if you click that it should let you browse around and find the xxxxxx\Angelica login. I guess you put Angelica in the username spot - that's free text I believe (I never really put users in this way - unless I'm creating SQL AUTHENTICATION USERS).
ok, that is all done using the last method. Guess that's there is to it then.
Thanks a lot Szlamany.
Now I set up 2 new Logins were, one for the user to login the database through the system and the other for a sysadmin.
However when I try to seet X.XXXXX\USER as a sysadmin I am getting this error and dont know what it is exactly.
Hi help,
Im completely blocked out of the database and the Server. I get this error on clicking the properties of the Server and Database.
Anythng I can do pls?
I told you not to change the default database...
http://forums.microsoft.com/MSDN/Sho...67068&SiteID=1
Google also found lots of other sites - but I think this one clearly tells you how to change the default database back to something more reasonable.
I had done two horrible mistakes, one I locked out XXXXX\user by trying to mess with the dbright and deleted owner by mistake and I also changed the default. The last one I knew how to fix but the other completely shut me out. Good thing I had another admin user account and logged int he database through that one.
I better leave experimenting for after my project! Thanks Szl, am saving some points for rating your help!