[RESOLVED] Need some good advice on Windows application & SQL SERVER database Security

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?

Quote:

---

Originally Posted by angelica

1. What goes into SQL Native Client cos the intellisense say : Expression expected

---

If you are using SQLConnection object than you dont need to specify Driver details.

Quote:

---

Originally Posted by angelica

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 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.

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)

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>

---

Is your machine name ANGELICA? That is a SQLEXPRESS instance - right?

Try making yours be

Code:

---

connectionString="Server=Angelica\SQLExpress; Initial Catalog=BooksControl; Integrated Security=SSPI"

---

That trailing ; (semi-colon) could be your whole problem

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?

Quote:

---

Originally Posted by angelica

Username : ANGELICA\user ---ANGELICA is CAPS
Authentication: Windows
Computername : ANGELICA

---

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...

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.

Quote:

---

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...

---

Ok so you say I need to add XXXXXX\user in the SSMS security. I had a look and I get this: jpg

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.

Quote:

---

pinging helps eliminate/trace client or network related issues. We do a tnsping on our Oracle DB listener

---

what is this for? Sorry simple terminology pls its the first time my using a SQL SERVER.!

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?