Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Total newb question Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 1:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 30, 2012 12:56 AM
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
Post #1389399
Posted Tuesday, November 27, 2012 1:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:59 PM
Points: 1,376, Visits: 2,589
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
Post #1389405
Posted Tuesday, November 27, 2012 2:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 30, 2012 12:56 AM
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!
Post #1389422
Posted Tuesday, November 27, 2012 2:58 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:59 PM
Points: 1,376, Visits: 2,589
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
Post #1389428
Posted Wednesday, November 28, 2012 11:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 30, 2012 12:56 AM
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?
Post #1390014
Posted Friday, November 30, 2012 12:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 30, 2012 12:56 AM
Points: 4, Visits: 5
bump, anyone? should be an ez thing to disable right?
Post #1391086
Posted Friday, November 30, 2012 3:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 42,817, Visits: 35,939
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 2008, MVP
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

Post #1391180
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse