SQL Security Hole - am I vulnerable? - C# Discussion Boards

Re: SQL Security Hole - am I vulnerable?

5-Jul-04 13:04

I'm bran new to the world of SQL. I barely know what it is. I'm reading "Visual C# Step by Step" and the chapter, "Accessing Data with Web Forms" requires that I be running a SQL server.

I have had some difficulty doing this but I finaly logged in using the 'sa' account (Thank you Colin Smile | :)

). I now have a few security-related questions:

1. What is the difference between Windows Authentication Mode and Mixed Authentication Mode? I was unable to login when the authentication mode was set to Windows Mode so I had to change to Mixed Authentication.

2. Why couldn't I login under Windows Authentication Mode? Why did I have to change to Mixed Authentication Mode?

2. After changing the authentication mode to Mixed in the registry by shutting down the MSSQLSERVER service, changing the "LoginMode" to "3" in the registry, and restarting MSSQLSERVER, I changed my password from NULL with the following commands:

sp_password @old = null, @new = %my new password%, @loginname = 'sa'
go

Did I open myself to any security holes by changing to Mixed Authentication Mode? Or did changing my password from NULL solve the potential security issue?

Thanks,

-Ian

Mike Dimmick5-Jul-04 13:33

Mike Dimmick

5-Jul-04 13:33

OK, here we go:

#1:

Windows Authentication uses the account of the user running the process connecting to SQL Server (or, I think, the account being impersonated by the process). Mixed mode uses Windows authentication or SQL Server authentication, where you supply the username and password in the connection string. The difference is that with SQL Server authentication, the usernames and passwords are stored in the MSDB database, whereas with Windows authentication, the usernames and passwords are stored either in the SAM (Security Accounts Manager, a Windows subsystem which uses the registry as a back-end store) or in Active Directory (on a Windows 2000 or higher domain). The server machine and client machine have to trust each other - if they're not the same machine, they have to be part of the same domain or domains which trust each other.

#2:

sa is typically a SQL Server account, stored in MSDB. If you want to use Windows authentication, you have to specify Trusted Connection=yes in the connection string. This tells the client code to negotiate a trusted connection. If the server is set to Windows Authentication only, you can't supply a username and password. If you had the Trusted Connection setting enabled, and you still couldn't connect, we need to talk about logins.

It isn't enough just to have a Windows user account to connect to SQL Server. The server also needs to know that the user is permitted to use it. By default only the built-in Administrators group is given access. To add a Windows login to SQL Server, open Enterprise Manager and go to Security > Logins. Right-click a blank area and choose New Login.

You also have to choose the roles for your user. Typically you want to select the public role. This role is usually given only the permissions necessary to use the database, not to administer it.

This is the reason you shouldn't use the sa user from an application: it's too powerful. The sa user maps to the dbo role, and is a server administrator. This means that the user can drop tables, delete data, insert data, alter stored procedures, alter table structure, basically do anything to any database on the server. That's far too powerful for an application.

By default, the public built-in role has no permissions at all. To add permissions in Enterprise Manager, expand the database you want to give permissions on and select Roles in the tree. Double-click the public role then click Permissions. Check the boxes for the permissions you want a general user to have.

#3:

Mixed Mode is a bit of a flaw because passwords are transmitted in the clear on the network between the client and the server. Anyone with a packet sniffer can read the passwords. You also have to store the password somewhere on the local machine. It's possible to do this in a secure way but it's typically easier to go with Windows authentication. You definitely want a strong sa password so that you're not susceptible to password-guessing attacks.

Stability. What an interesting concept. -- Chris Maunder

Re: SQL Security Hole - am I vulnerable?

Ian Bowler5-Jul-04 21:32

Re: SQL Security Hole - am I vulnerable?

5-Jul-04 21:32

Wow. Many thanks to Mike for the great answers.

I've seen reference to Enterprise Manager in my SQL research but I don't think I have it. Unfortunately, I'm using MSSQL Desktop Edition (I think. The only GUI component I've seen runs in the systray and is called, "SQL Server Service Manager". I installed it from the SQL2000.msi package that gets installed with Visual Studio .NET Enterpise). How do I get a hold of Enterprise Manager? Many of the SQL solutions I find refer to it.

Many thanks,

-Ian

Colin Angus Mackay5-Jul-04 23:25

comparison of two strings - how to get a score

5-Jul-04 23:25

SQL Server Standard, Enterprise and Developer editions all have it. Also, the Evaluation edition that I posted a link to yesterday will have it also, that way you can down load the Evaluation and get to use it immediately (although if you are on a dial-up connection you may have to wait a while).

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

The Second EuroCPian Event will be in Brussels on the 4th of September

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

My Blog

rlacatus5-Jul-04 12:34

rlacatus

5-Jul-04 12:34

Hi folks,

I'm looking to find some code, or guidance for producing my own code, which would give me a score (say a percent value) on how similar two strings are. Any ideas are appreciated.

Note: I've found, online, something about a Trigram algorithm for the purpose, but couldn't understand it fully. It seemed to be looking more at the meaning of two sentences (i.e. if they contain synonyms), which isn't quite what I'm looking for.

As an example, if my comparison sentence is: "I'm looking for a needle in a haystack" then I'd like "I'm looking for needles in a haystack" to give me a better score than "I'm looking for needles in haystacks", but the scores should be pretty close.

Thanks

R

Re: comparison of two strings - how to get a score

rlacatus6-Jul-04 11:30

rlacatus

6-Jul-04 11:30

Hi folks,

Well, there seems to be little interest in the subject. Either that, or I wasn't very clear on what I wanted - or, few people had what to say of instructive on the matter. No matter the case, in case anyone is looking for a solution, here is what I've found:

There is, amongst other things, a good article online (a university paper in fact) that is easy to understand, to the point, and free to download from here:

http://arxiv.org/abs/cs.DS/0112022 (See PostScript or PDF link at bottom)

Anyway, based on this very good document, I've written a C# function to compare two strings, and give a percentage on how close the strings are to one another as such:

// Found http://arxiv.org/abs/cs.DS/0112022
// July 6, 2004

// From: qi xiao yang
// Date (v1): Fri, 21 Dec 2001 05:58:12 GMT (250kb)
// Date (revised v2): Tue, 25 Dec 2001 21:29:22 GMT (294kb)

// Faster Algorithm of String Comparison
// Authors: Qi Xiao Yang, Sung Sam Yuan, Lu Chun, Li Zhao, Sun Peng

private double StringCompareValue(string str1, string str2)
{
string strX, strY; // smaller and bigger of the strings, respectively
double val; // return value
int window, wPos; // window width and position
double SSNC = 0; // value used to calculate return in the end

// get smaller & bigger strings sorted out
// also, perform lower case conversion
if(str1.Length < str2.Length)
{
strX = str1.ToLower();
strY = str2.ToLower();
}
else
{
strX = str2.ToLower();
strY = str1.ToLower();
}

// initialize
window = strX.Length;
wPos = 0;

// while the window exists and the smallest string exists
while(window > 0 && strX.Length > 0)
{
// while the window doesn't overlap the end of the string
while((wPos + window) <= strX.Length)
{
// get string to compare to
string comparer = strX.Substring(wPos, window);
// start comparison point at zero
int cPos = 0;

// loop through second string to get all possible comparisons
while(cPos + window <= strY.Length)
{
// if comparisons match
if(comparer == strY.Substring(cPos, window))
{
// update SSNC Value
SSNC += Math.Pow((2 * window), 2);

// remove "used characters"
strY = strY.Remove(cPos, window);
strX = strX.Remove(wPos, window);

// force window position to stay same place (moved forward again at end of loop)
wPos--;;

// continue search with next window position
break;
}

// move right on longer string
cPos++;
}

// move right on shorter string
wPos++;
}

// decrease window size - reset to beginning of string
window--;
wPos = 0;
}

// get percentage value
val = Math.Sqrt( (SSNC) / Math.Pow( (str1.Length + str2.Length) , 2) ) * 100;

return val;

}

If anyone finds this useful, you're free to use for any purpose, with the condition that the original article is referenced. I take no credit for the code, it is simply typing out the C# equivalent of the pseudo-code found in the article, which in turn is found free for use online.

If you have any comments, please post - I'm curious of your thoughts. Also, if there are any bugs in the code, let me know - I haven't tested too thoroughly.

All the best,

R

DataGrid current row

Christian Graus5-Jul-04 11:13

Christian Graus

5-Jul-04 11:13

I've got a datagrid, and when the user double clicks to select a row, I want to bring up a new grid, in a dialog, with data based on the item selected. The CurrentCell property works fine until I click on a header to sort the grid, then it seems to continue to point to the cell that WAS in that location prior to the sort. How do I handle this ?

Christian

I have drunk the cool-aid and found it wan and bitter. - Chris Maunder

Re: DataGrid current row

Christian Graus5-Jul-04 12:14

Christian Graus

5-Jul-04 12:14

It's OK, I converted a VB.NET code example, and I've got it working. What the hell is a currency manager ?

Christian

I have drunk the cool-aid and found it wan and bitter. - Chris Maunder

Re: DataGrid current row

Heath Stewart5-Jul-04 18:08

Heath Stewart

5-Jul-04 18:08

It manages the current row in a data source and is used for data binding, as is the PropertyManager (both derivatives of the BindingManagerBase).

Microsoft MVP, Visual C#
My Articles

Re: DataGrid current row

sreejith ss nair6-Jul-04 2:03

sreejith ss nair

6-Jul-04 2:03

hai there,

i already posted answer for this same query . so you can refer this message for further references.please read the discussion that held between me and the person who posted same queary posted some time earlier.

url : http://www.codeproject.com/script/comments/forums.asp?msg=821632&forumid=1649&Page=2&userid=854929&mode=all#xx821632xx

Please feel free to contact.

Sreejith S S Nair - Bangalore

Accessing Data with Web Forms

Ian Bowler5-Jul-04 10:38

5-Jul-04 10:38

Hello,

I'm a beginner reading "Microsoft Visual C# .NET Step By Step". I'm in Chapter 29, "Accessing Data with Web Forms". According to the author, I need to grant SQL access to the ASP.NET service by completing several steps, the first of which is:

In the command prompt window, type osql -E -S your_computer\NetSDK where your_computer is your computer name. This command starts the osql query tool, allowing you to enter SQL commands. You will see the prompt 1>.

I currently have MSSQLSever 2000 running in the systray. when I follow the above step, I get the following error message in the command prompt:

[DBMSLPCN]SQL Server does not exist or access denied.
[DBMSLPCN]ConnectionOpen >.

I've never used SQL before. I just installed it from the SQL2000.msi.

Thanks in advance!

-Ian

Colin Angus Mackay5-Jul-04 11:13

5-Jul-04 11:13

SQL Server actually runs as a Service. What you see in the Systray is the Service Manager which allows you to start, stop and pause the SQL Server service on the server you specify.

Check that the service is running.

Also, did you install your SQL Server as a named instance? That is what the "NetSDK" bit means - you can install SQL Server many times on a single machine. By default the installation will create an instance without a name. The Service Manager will probably list all the instances you have (I don't recall of the top of my head, and I am only running one instance that has no name)

Does this help?

Ian Bowler5-Jul-04 11:41

5-Jul-04 11:41

Colin,

According to the SQL Server Service Manager, a SQL Server is running on my local machine. I installed MSSQL Server 2000 by running Program Files\Microsoft Visual studio .NET\FrameworkSDK\Samples\Setup\msde\setup\sql2000.msi. This installation package did not ask me to supply a server name.

When I removed the "NetSDK" bit, I got a different error:

Login failed for user 'IANANDKRISTA\Ian'.

Colin Angus Mackay5-Jul-04 11:56

5-Jul-04 11:56

Okay, you have at least established a connection to your SQL Server - but you also need to supply the correct userid and password. All SQL servers will have an "sa" account (System Admin) and if you haven't changed it then chances are its password will be blank.

Also, since your using MSDE (the Desktop Edition of SQL Server) you don't have all the nice GUI bits that make changing passwords, adding users and so on easy.

Once your logged in as sa, you need to run the query:

exec sp_grantlogin N'IANANDKRISTA\Ian'

Then you can log in automatically while you are logged into Windows.

Does this help?

Disclaimer: I use the Enterprise edition of SQL Server so I'm more used to the GUI which handles all this stuff - so, appologies in advance if I've missed something.

Ian Bowler5-Jul-04 12:03

5-Jul-04 12:03

How do I log in as the system administrator in order to run the query you suggest? Also, does the enterprise edition of SQL Server come with Visual Studio .NET? If not, where do I get it? (it sounds easier to use than the Desktop Edition, which I didn't even know I was running).

Thanks,

-Ian

Colin Angus Mackay5-Jul-04 12:22

5-Jul-04 12:22

According to the On-line help replacing the -E with -U sa from your original command line should do the trick.

Ian Bowler wrote:
Also, does the enterprise edition of SQL Server come with Visual Studio .NET?

The Enterprise edition is the version that is installed in companies' back-offices and is kind of expensive. The Developer Edition is identical except for the licence and maybe one or minor details. IIRC the Enterprise Edition must be run on a Server Edition of Windows, while the Developer Edition can run on a regular Windows 2000/XP machine.

The Developer edition also comes with some levels of MSDN subscription.

You can also download a 120-day trial version from Microsoft: http://www.microsoft.com/sql/evaluation/trial/default.asp[^] Which will give you everything you need. And it should be easy from the installation to name the server to fit with the examples in your book.

Does this help?

Ian Bowler5-Jul-04 12:46

5-Jul-04 12:46

You have been extremely generous with your time.

I have successfully logged in to the 'sa' account. However, I do have a security question now. I am going to post this question as a new topic.

Thank you so much for your help. Without your answers, I'd still be nowhere.

Smile | :)

-Ian

Colin Angus Mackay5-Jul-04 13:03