Linked Server Connection and Database Permissions?

I've changed the linkedserver code from 'Integrated Security' to 'UserId/Password'.

Now I cannot run the sql query.

Q. Do I have to login to the db as well with more code?

[Current Code]
EXEC sp_addlinkedserver
@server = 'TESTPC',
@srvproduct = '',
@provider = 'SQLNCLI',
@provstr = 'DRIVER={SQL Server};SERVER=TEST01\SQLEXPRESS;User Id=TESTPC;Password=123;'  // Link works

Select * From TESTPC.HousingDatabase.dbo.SalesTable // This code now fails

Help...


LVL 1
kvnsdrAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I have added account 'admindev' to the test pc (testpcadministrator) SQL Server as dbo, sysadmin, mapped to HousingDatabase, but the above error seems to say that NO user is named to login.#
actually, that remote server is probably configured to only accept windows logins, check that setting. if it is not in mixed authentication mode, sql logins will not be accepted (although you can create them).



now: why don't you create the linked server with windows authentication anyhow, as you love that?

cn = new SqlConnection(@"server=(local)\SQLEXPRESS; persist security info=False;integrated security=SSPI;initial catalog=HousingDatabase");
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn; cn.Open();
cmd.CommandText =
" If Not Exists(select * from master..sysservers where srvname = TESTPC) " +
" Begin Exec sp_addlinkedserver @server = 'TESTPC', @srvproduct = '', @provider = 'SQLNCLI', " +
" @provstr = 'Provider=SQLOLEDB;SERVER=TESTPC\SQLEXPRESS;integrated security=SSPI;' END";
cmd.ExecuteNonQuery();

cmd.CommandText = " Select * From TESTPC.HousingDatabase.dbo.SalesTable";
cmd.ExecuteNonQuery();

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>// This code now fails

with what error?
0
 
kvnsdrAuthor Commented:
I need to include more explaination.

I'm trying to connect from a WinApp and having trouble since I change the 'Integrated Security'.

[More code]
cn = new SqlConnection(@"server=(local)\SQLEXPRESS; persist security info=False;integrated security=SSPI;initial catalog=HousingDatabase");
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn; cn.Open();
cmd.CommandText =
" If Not Exists(select * from master..sysservers where srvname = TESTPC) " +
" Begin Exec sp_addlinkedserver @server = 'TESTPC', @srvproduct = '', @provider = 'SQLNCLI', " +
" @provstr = 'DRIVER={SQL Server};SERVER=TESTPC\SQLEXPRESS;User Id=testpcadministrator;Password=x;' END";
cmd.ExecuteNonQuery();

cmd.CommandText = " Select * From TESTPC.HousingDatabase.dbo.SalesTable";
cmd.ExecuteNonQuery();

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I can only repeat: what is the error, and on which line does it error out exactly?
0
 
kvnsdrAuthor Commented:
oops,

"SqlException: Login failed for user ' '. The user is not accociated with a trusted SQL Server connection."

I have added account 'admindev' to the test pc (testpcadministrator) SQL Server as dbo, sysadmin, mapped to HousingDatabase, but the above error seems to say that NO user is named to login.

I'm so used to using Windows Integrated that I'm lost without it.

Just thought it would be more secure to have users actually login to SQL Server without having to make them Windows users too.

I think I  may need to code C# to login to the remote as well?



 

0
 
kvnsdrAuthor Commented:
>Just thought it would be more secure to have users actually login to SQL Server without having to make them Windows users too.<

Just tell me if I'm asking for the impossible considering my C# coding method.


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, if you want to use sql logins on the remote server, ensure that the remote sql server is in mixed authentication method.
0
 
kvnsdrAuthor Commented:
I'll simply use the Windows Authentication as it seems to be the only solution to this particular issue.

Thanks for all your help.........
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.