Solved

remote connection to SQL server issue

Posted on 2009-05-09
6
487 Views
Last Modified: 2013-12-14
Im using SQLAPI++ to connect unmanaged c++ to sql.  On local machine works perfect. I can get as many instances open as I need all looking at the SQL, all connections fine.

I then setup sql server 2008 to handle remote connections by enabling  Named Pipes in the configuration manager and restarting as I learned by default it doesnt allow remote connections.

from another machine i want to connect to the database.  I can ping the server CAVIARIS  the code sees the server but the logon fails?  I dont have any passwords, its windows authentification, I dont care about what authentification it has to be so if I need to change tell me.  

It fails by saying Logon failed for user CAVIARIS\Guest  

I want a number of machines to connect to SQL but Im stuck on the connections.  Do I have to do something in Serrver Management studio?  ie do I have to create a logon before I can go to the client machine and logon, is that the basic philosophy of how this works?  

I need a generic logon for any machine that accesses SQL database, Im hoping I dont have to manually create a new one each time as Im trying to make a cluster of servers all see the same database.  Can someone please walk me through a procedure to get me connected.  its XP  machines are in a network workgroup configuration.

Ive tried scores of combinations but I think Im missing a more general point, thanks
0
Comment
Question by:TheMoog
  • 2
  • 2
  • 2
6 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 24345072
Hi,

Yes you have to do something in SQL Server Management Studio.

You need to create a SQL Login. The SQL Login can use SQL Authentication or Windows Authentication, and in the last case be a Domain User or Domain Group.

The login then needs to be mapped to a user in a database, and given rights to that database. If you use a Domain group, this is a once per new server per new database - then might need some maintenance/adjustment etc.

HTH
  David
0
 

Author Comment

by:TheMoog
ID: 24348014
I think I may already have done the first part.  I created the database in VS 2008.  When I open the datbase in Server Management Studio I can see the login details
Server Type: Database Engine
Server Name: CAVIARIS
Authentification: Windows Authentification
User Name: CAVIARIS\Administrator
No password.

how do you you map the login to a user in a database and give them rights?

Thanks
0
 
LVL 35

Expert Comment

by:David Todd
ID: 24349983
Hi,

In SSMS Query Explorer, go to the database in question, navigate to security and users, right-click and select New User.

You will see other users already there.

I suggest to get started that you ned to make this user a member of db_datareader.

EVERY object will need rights assigned, and while this can be done at the schema level in SQL 2005 and above, it will still need to be done. Please do avoid the temptation of simply making the new user a member of dbo. This is not the sort of permissions you want an ordinary user to have.

Cheers
  David
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Expert Comment

by:Kanchipuramdeena
ID: 24353017
You need to start the SQL Browser service and enable named pipes

For more info.. refer the following url

http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/
0
 

Author Comment

by:TheMoog
ID: 24353672
OK Ive been a few hrs into this, heres where Im at.  With the help of the last post I have managed to get remote connections operational.  I thought I did this with reading from internet and enabling TCPIP but that was only half the story I didnt know about the server browser, so thats all operational now.  

From another machine I can ping the server.  I discovered something called a log file viewer where I can see why the client wont connect.  I have an error message and have looked at the help but Im baffled by what to do?

Error 18456 Severity 14 State 11.  Token based server validation failed with an infrastructure error.  So reading the help it says I do not have permission to login to server.  OK

How do I give myself permissions to loginto my server?

I right clicked on guest -> properties and clicked on every single button to see if thats it but im unsure and maybe causing more drama than just asking you guys?
0
 
LVL 1

Accepted Solution

by:
Kanchipuramdeena earned 250 total points
ID: 24429932
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question