Linked server setup – Back to basics (error 7399)

I recently received this question:

Why am I getting an error ‘Invalid authorization specification’. I used my SA account, but i keep getting the error that the credentials specified are incorrect.

There is a very simple answer to this question, which I will share with you.

The error you get when the credentials are invalid:LSSLinked server is a connection between two servers in your network. If you want to connect the servers, the specified credentials need to be known on both servers, So, unless you use the same password for sa acounts on all your network server, which is never recommended in the first place! I advise you to create a specific Linked server sql account, for example I created a LS_2014 account on both servers and specified on which databases the Linked server is allowed to read and select data, as you can see in the example below.

ls_account

ls_acc

This keeps your databases secure and easier to manage.

You could also set it to use your current logged in account, but you need enough permissions on all servers and depending on who is using the SQL server, the linked server might not be available.

If you use the account specifically created for the Linked server and test the linked server account, the connection will be successful and you will see the table you granted read permissions on is available in the linked server dropdown.

Create and restore a database with multiple secondary data files

SQL databases are all about performance, if databases grow very large, it might be a good idea to spread your files across multiple disks.

A database can consist of three files, .mdf, .ldf and .ndf, in which .ndf is a user defined secondary data file, these files can be created to split up a fast growing .mdf. For example you create 3 files, Data1.ndf, Data2.ndf, and Data3.ndf spread on three disk drives and assigned to a filegroup e.g.’fg1′. Tables can be created specifically on the filegroup ‘fg1’. The queries for data from the table can be spread across multiple disks; this will improve your database performance.

Here’s a script to create a database with multiple filegroups:

 

Need a quick restore of a database with the data spread across multiple disks onto a new server, here’s a simple script I wrote. Just replace name and location and you’re ready to go.