Connect To Remote SQL Server 2000 1

[nidgep]

Hi

Can anyone tell me how to connect to a remote instance of SQL Server 2000?

I have created a SQL Server login with a password andused the following connection details..


oConn.Open "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"


I am getting an error saying that the login is not trusted.
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'myUsername'. Reason: Not associated with a trusted SQL Server connection.

The remote server is available through the internal network and can be pinged OK using the IP address provided.

Can anyone explain what is wrong?

Thanks in advance to any help offered.

 

[LogicalmanUS]

nidgep,

From the error message being returned, it appears you can contact the SQL instance, but you are failing on SQL Server security.
It may sound simple, but have you checked that the login credentials have permissions on the server? (this can be achieved by opening an instance of Query Analyzer and using the same credentials).
You may also try using a 'Trusted Connection' as long as you have a secure login to the application.
What application are you using for this connection?

Logicalman

 

[nidgep]

Logicalman

The account has only been given access to a single database with Public, DataReader and DataWriter roles.

I have tried your suggestion to use the SQL Query Analyzer to attempt to connect and indeed it failed - giving roughly the same message as before. Thanks.

If I connect using my own credentials [Windows Integrated] through Query Analyzer I can connect no problem.

How do I solve this now?
I thought that creating the account and giving some sort of access would be enough!

By the way I am attempting to connect to the remote server through an ASP page running on an internal webserver.
A VPN connection allows communication currently.


thanks for your help so far...

 

[JamesLean]

It looks very much like your SQL Server is set to only use Windows Authentication. You need to change it to mixed mode (Windows and SQL Server authentication) to be able to use a username/password like that.

You can check this from Ent Mgr by right-clicking the server and going to properties - security tab.

--James

 

[nidgep]

James

Yes you were spot on!
Should have checked that before but I just assumed that because it was a remote server that both methods of authentication would have been setup previously.

Thanks for your help and also thanks to LogicalMan.

Cheers guys.

 

[nidgep]

HI Guys

...continuning from the question I posed....
The connection to the remote server now works fine every time so far.

The login I have created, currently has only been given access to the Northwind database.

With this in mind, how can the same connection credentials be used to get access to other databases on the same SQL Server instance. Such databases as Master, msdb, Pubs, Model..etc?

If I create a user database for example UserDB1 then the login cannot connect.

Can anyone explain why this is so?
More specifically how can this be stopped and limited only to the databases that have been explicitly selected?

Thanks

 

[LogicalmanUS]

nidgep,

Security in SQL Server can be as easy, or hard, as you want it.

If you have created an application login, using EM, you will see it under Security - Logins.

All Logins have default databases. You can see this by opening up the logins, and double clicking the particular login name.
First you will see the General Tab. This holds the password, default database and default language.
The Server Roles tab, will show what priveledges the login has on the Server. Be wary of selecting anything on this tab.
The Database Access tab is the one you need. Here you can select what databases the login can see, and what database roles it enjoys.
Note that all this does is allow the login access to the public roles on the database.

To give the login access to a particular object (table, SProc, View etc) on the database, you need to open that database up, Click Users, double click the login name, click Permissions on the Dialogue box, and you will see all the objects listed. You can now set permissions for the objects.

You may also set the column level permissions here. This is useful if you have certain columns that the login shouldn't see, and some it can. Click the columns button on the dialogue box, and then set the individual columns. WARNING, If you later click SELECT for the whole table in this dialogue box, you will override the column permissions settings.

Hope this gives you some insight into setting up login security.

Logicalman

 

[nidgep]

Thanks Logicalman,

What I should have said was that I need to create a login that can be used from an ASP page to connect to the remote server. This login should ONLY have access to the specified database to which it has been specifically been given permissions on.
As you have said, creating a login causes it to have SELECT permissions under the guise of the PUBLIC role in other databases such as MASTER and MSDB for example.

What I would like to do is to stop this access under the guise of the PUBLIC role in ALL other databases but only for the new user I am creating.

I presume that in order to achieve this I would have to
create my own version of the PUBLIC role which has the same access rights and then remove or revoke all access rights for the PUBLIC role in all databases and then assign new logins /accounts to the 'NEW_PUBLIC' role as and when they are needed. I realise that the PUBLIC role is intended to make basic administration rights easier to manage as it is the default, but I want to restrict access to the remote database as much as possible as it resides on a web server. This limited and restricted access will include the use of VIEWS and STORED PROCEDURES etc.

Does this make sense, is it possible or is it just too unmanageable? Are there any draw backs to this methodology?

There will NOT be many logins or accounts to manage for this instance of SQL

Thanks for any advice that you have to offer.

nidgep

 

[JamesLean]

The only time a login gets default permissions in a database is when the DB has a guest user. Otherwise you have to explicitly create a user in the DB for the login and can then assign permissions.

Once you have created a user, it is automatically added to the public role in that db. You can use this role to provide permissions that you want ALL users to have.

So for your situation, make sure your DBs don't have a guest user, create the login and then only give it access to the specific DB. Obviously you can then set up specific permissions that it needs.

--James

 

[LogicalmanUS]

nidgep,

No problem at all. You cannot drop the Public role from a login, but you need not fret. Wjilst you do not give that login permissions to other databases, it cannot access them per se.
Once you have created the login, you can open the database it is assigned to, slect Users and double click the Login name. Click 'Permissions' and you can permie SELECT permissions only on those objects you wish.
The Public role does not have the same rights as you assign the Login, unless you specifically assign those rights. Therefore you can be assured security is (reasonably) tight for your application.

Roles are usefull when you have a number of Logins and need them to inherit permissions by assigning them to a Role and then assigning the Role permissions on Objects. This allows for an administrator to easily manage permissions for Logins without changing each one individually.

So remember, Logns can have permissions on objects, or, if they are not assigned any object permissions, they can be assigned to a Role which has such permissions.

Hope this clears a few more hurdles for you,

Logicalman

 

[nidgep]

Big thanks to Logicalman and JamesLean for your help.

It was much appreciated and has cleared up my understanding quite a bit regarding security involving logins and permissions.

I wouldn't say that I am anywhere near being proficient, but a little more confident at least!

Thanks again.

nidgep