Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Total newb question


Total newb question

Author
Message
davidfes88
davidfes88
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
Hi guys, I'm very new to sql and MSSM 2008.

I have management studio 2008.

I made a program to read my database.

I enabled server authentication and made a strong PW for the 'sa' account. (why i enabled SA, beacuse I thought to remotely login from other networks off site, I needed this?, correct me if im wrong!)

I opened port on my router to allow myself to remotely login and see my database entries from any computer using a little program/tool I made.

When I logged in, it let me log in and see my database, even with the WRONG username and password!

Is there some sort of guest account enabled? I feel like this is a security risk! I don't want any computer or anyone being able to see what is in the database unless a password is matching a database user. Basically at this point, if they know my IP address, they can login and see what is in my tables. I haven't tried to update or insert records from the remote computer yet.

But the goal is to have remote access from other networks off site being able to ONLY see what is in the database by using a username/pw, WITHOUT being able to update/insert records or edit or delete anything.

Is there a way to manage all this inside MSSM? because I don't know any other way since im totally a newb at this point... Trying to learn all on my own.

Thank you so much
Robert klimes
Robert klimes
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1847 Visits: 3408
I enabled server authentication and made a strong PW for the 'sa' account. (why i enabled SA, beacuse I thought to remotely login from other networks off site, I needed this?, correct me if im wrong!)


You should create a login to use for yourself and not use SA. SA would give you too much permission for what you require.


When I logged in, it let me log in and see my database, even with the WRONG username and password!


When you are logged in try running the following to see if you are logged in with the user you think you are.
select suser_sname()



There is a guest database user but not a login for the server

this is an example for creating a log and user. and giving that user read permissions
CREATE LOGIN [<username>] WITH PASSWORD=N'<password>', DEFAULT_DATABASE=[<dbname>];

USE [<dbname>];
CREATE USER [<username>] FOR LOGIN [<username>];

EXEC sp_addrolemember 'db_datareader','[<username>]';



Bob
-----------------------------------------------------------------------------
How to post to get the best help
davidfes88
davidfes88
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
Bob, thanks for the reply 1st of all.

So I disabled SA account and made my own account which has only 'public' as a server role. and I set the user mapped to the databases I want them to go to.

I logged in using a SQL client on my cell phone actually. I was on a different network and was not using windows authentication. I tried to access my databse and it allowed me. I tried a different database(which was setup by someone else I think, cuz someone else set this whole computer sql up I should mention.) and it said not enabled for GUEST ACCOUNT.

So I assume it is allowing guest account somehow, but in MSSM when I look up users, I do not see any account labeled as guest!
Robert klimes
Robert klimes
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1847 Visits: 3408
well lets give this a try.

run the following in SSMS when you are logged in to your remote mobile client
SELECT  session_id,login_name,host_name
FROM sys.dm_exec_sessions
where session_id > 50



it will list all users currently logged into your SQL Server instance and the host they are connected from. Not sure but it will probably be null for your remote client. you can use this to identify what user is being used when you login.

A guest account should exist in all databases but it should be disabled by default. Since you are unable to see the guest account i have to ask what are your permissions on the server and database?

as for SA, i wasn't suggesting to disable the account just not to use it in the context that you were.

Bob
-----------------------------------------------------------------------------
How to post to get the best help
davidfes88
davidfes88
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
So I found out it IS the guest account. But in MSSM there is no guest account listed.

I am indeed administrator, and on the actual physical computer that has mssm installed. Everything I do, from adding an admin to deleting a user I am allowed to do in MSSM without restriction. So I know I have full admin rights. I just can't see the guest account option.

Thoughts?
davidfes88
davidfes88
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
bump, anyone? should be an ez thing to disable right?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47175 Visits: 44355
There is no guest login. If you don't have either a username and password or a windows login granted access to the DB, you cannot log in to SQL.

There are guest users in databases, that's for when someone has a valid login but no user mapped to that DB. Typically the guest user has no rights.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search